| Groups | Blog | Home |
sql server clients : ORDER BY CLAUSE
-- Andrew J. Kelly SQL MVP [quoted text, click to view] "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... >> 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. >> > >> > >> > >> > >> >> > >
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 [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:OHJU2TiYFHA.1040@TK2MSFTNGP10.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... > >> 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. > >> > > >> > > >> > > >> > > >> > >> > > > > > >
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 [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:OHJU2TiYFHA.1040@TK2MSFTNGP10.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... > >> 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. > >> > > >> > > >> > > >> > > >> > >> > > > > > >
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 [quoted text, click to view] "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. > > > >
[quoted text, click to view]
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 [quoted text, click to view] "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... > > > > 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. > > > > > > > > > >
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 [quoted text, click to view] "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... >> 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... >> >> 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. >> >> > >> >> > >> >> > >> >> > >> >> >> >> >> > >> > >> >> > >
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 ? [quoted text, click to view] "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 > 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... > >> 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... > >> >> 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. > >> >> > > >> >> > > >> >> > > >> >> > > >> >> > >> >> > >> > > >> > > >> > >> > > > > > >
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 [quoted text, click to view] "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... >> 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... >> >> 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... >> >> >> 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. >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> >> >> >> >> >> >> > >> >> > >> >> >> >> >> > >> > >> >> > >
If you want help here, please
1. Explain exactly what "not working" means. 2. Give the CREATE TABLE and CREATE VIEW statements for the tables and views in your query 3. Show what result you get and what result you think you should get. 4. Post your question only once, to keep everything in one thread and so you don't waste everyone's time. When you say that a query with invalid syntax is "working", like this: Working:- select top 1 LInfoID from Medicalhistory order by 1 LInfoID desc you obviously aren't bothering to put in much of your own effort to solve the problem without wasting others' time. Steve Kass Drew University [quoted text, click to view] Joh wrote:
>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 > > >>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... >>> >>> >>>>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... >>>>> >>>>> >>>>>>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. >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > >
Don't see what you're looking for? Try a search.
|
March 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |