ORDER BY CLAUSE

Working Fine : select top 5 * from MedicalHistory
Doesn't work : select top 5 * from MedicalHistory order by 1
Doesn't work : select top 5 * from MedicalHistory order by 4
Doesn't work : select top 5 * from MedicalHistory order by 2
MedicalHistory is basically a Partition view... can any one his or her
idea... what the problem is in order by clause.
First off I recommend you always use the actual column name instead of the
ordinal number. But what column is the indexed view clustered on? If it is
not 1,2 or 4 then you will scan the whole table and do a sort.
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:u9Xy9vhYFHA.3840@.tk2msftngp13.phx.gbl...
> Working Fine : select top 5 * from MedicalHistory
> Doesn't work : select top 5 * from MedicalHistory order by 1
> Doesn't work : select top 5 * from MedicalHistory order by 4
> Doesn't work : select top 5 * from MedicalHistory order by 2
> MedicalHistory is basically a Partition view... can any one his or her
> idea... what the problem is in order by clause.
>
>
|||Joh wrote:
> Working Fine : select top 5 * from MedicalHistory
> Doesn't work : select top 5 * from MedicalHistory order by 1
> Doesn't work : select top 5 * from MedicalHistory order by 4
> Doesn't work : select top 5 * from MedicalHistory order by 2
> MedicalHistory is basically a Partition view... can any one his or
> her idea... what the problem is in order by clause.
What do you mean "doesn't work"? Does the command fail or return results
that you believe are inocrrect? In general, you should avoid SELECT *
syntax and spell out the columns you need back. What happens if you
supply the column name instead of the ordinal position in the view?
When using TOP, you need an Order By or else you might get different
results with each execution.
Can you provide the DDL for the view.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Working:- select top 1 MedID, LInfoID from Medicalhistory order by 1
Not working:- select top 1 MedID, LInfoID from Medicalhistory order by 1
desc
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uqTgh5hYFHA.2884@.tk2msftngp13.phx.gbl...
> First off I recommend you always use the actual column name instead of the
> ordinal number. But what column is the indexed view clustered on? If it
is
> not 1,2 or 4 then you will scan the whole table and do a sort.
> --
> Andrew J. Kelly SQL MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:u9Xy9vhYFHA.3840@.tk2msftngp13.phx.gbl...
>
|||What does that have to do with my question?
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:emJE77hYFHA.2884@.tk2msftngp13.phx.gbl...
> Working:- select top 1 MedID, LInfoID from Medicalhistory order by 1
> Not working:- select top 1 MedID, LInfoID from Medicalhistory order by 1
> desc
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uqTgh5hYFHA.2884@.tk2msftngp13.phx.gbl...
> is
>
|||Note:
1) Non Clustered Index on MedID
2) Clustered Index on LInfoID
Working:- select top 1 MedID, LInfoID from Medicalhistory order by 1
Working:- select top 1 LInfoID from Medicalhistory order by 1 LInfoID desc
Not working:- select top 1 MedID, LInfoID from Medicalhistory order by 1
desc
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OHJU2TiYFHA.1040@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> What does that have to do with my question?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:emJE77hYFHA.2884@.tk2msftngp13.phx.gbl...
it[vbcol=seagreen]
her
>
|||No index on view... created indexes on the Partitioned tables but in the
view I used table like that
select col1,col2 from MedicalHistory_2003
UNION ALL
select col1,col2 from MedicalHistory_2004
UNION ALL
select col1,col2 from MedicalHistory_2005
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OHJU2TiYFHA.1040@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> What does that have to do with my question?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:emJE77hYFHA.2884@.tk2msftngp13.phx.gbl...
it[vbcol=seagreen]
her
>
|||My mistake. All this time I have been thinking you were using an Indexed
view even though you did state it was a partitioned view. The index should
be able to be used regardless of ASC or DESC. Does it make a difference if
you specify the column name instead of the Ordinal? I am not sure why it
would react that way. You might consider a covering index if you only have
the two columns as well.
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:e7h85ZiYFHA.3488@.tk2msftngp13.phx.gbl...
> No index on view... created indexes on the Partitioned tables but in the
> view I used table like that
> select col1,col2 from MedicalHistory_2003
> UNION ALL
> select col1,col2 from MedicalHistory_2004
> UNION ALL
> select col1,col2 from MedicalHistory_2005
> Thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OHJU2TiYFHA.1040@.TK2MSFTNGP10.phx.gbl...
> it
> her
>
|||I am still wondering if I use only column1 which has a clustered index then
it works fine when I tried to use Column2 which has non clustered index then
it doesn't work...
it doesn't make sense like if I wanna select four columns so I have to index
over all the four columns...
Like
select column1 from MedicalHistory order by Column1 Desc (working perfect -
column1 has a clustered index)
select column1, column2 from MedicalHistory order by Column1 Desc (doesn't
get the result - column1 has a clustered & column2 has non clustered index)
Any idea what I can do now ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OoNdJ0jYFHA.2756@.tk2msftngp13.phx.gbl...
> My mistake. All this time I have been thinking you were using an Indexed
> view even though you did state it was a partitioned view. The index
should
> be able to be used regardless of ASC or DESC. Does it make a difference
if
> you specify the column name instead of the Ordinal? I am not sure why it
> would react that way. You might consider a covering index if you only
have[vbcol=seagreen]
> the two columns as well.
> --
> Andrew J. Kelly SQL MVP
>
> "Joh" <joh@.mailcity.com> wrote in message
> news:e7h85ZiYFHA.3488@.tk2msftngp13.phx.gbl...
by[vbcol=seagreen]
of[vbcol=seagreen]
If[vbcol=seagreen]
or
>
|||Without actually being there and seeing the plans and understanding exactly
what is going on it is tough. I have never used a DPV in production as
their uses are limited. The partitioning in SQL2005 is much cleaner and
useful in my opinion. Sorry I don't have a better answer.
Andrew J. Kelly SQL MVP
"Joh" <joh@.mailcity.com> wrote in message
news:urKlmqrYFHA.612@.TK2MSFTNGP12.phx.gbl...
>I am still wondering if I use only column1 which has a clustered index then
> it works fine when I tried to use Column2 which has non clustered index
> then
> it doesn't work...
> it doesn't make sense like if I wanna select four columns so I have to
> index
> over all the four columns...
> Like
> select column1 from MedicalHistory order by Column1 Desc (working
> perfect -
> column1 has a clustered index)
> select column1, column2 from MedicalHistory order by Column1 Desc (doesn't
> get the result - column1 has a clustered & column2 has non clustered
> index)
> Any idea what I can do now ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OoNdJ0jYFHA.2756@.tk2msftngp13.phx.gbl...
> should
> if
> have
> by
> of
> If
> or
>