Brad Bansner
asked on
MS SQL order by with "over" statement and row_number()
I have a query I use frequently that someone helped me write several years ago, which I often create variations on. I thought I understood exactly what it does, but I am finding a discrepancy with the "order by" that I don't understand.
This query does what I want, which is simply an alphabetical sort on the "description" column:
RESULT:
This query returns the same two results, but the "order by" is backwards:
RESULT:
The only difference between the two is the "itemid" column present in the "select" clause (twice). I'm not understanding why selecting that column should cause the sorting to be backwards.
Thank you!
This query does what I want, which is simply an alphabetical sort on the "description" column:
select distinct
description
from
(select row_number() over (order by description) as rownum, description
from tbl_product
where (itemid='LUGS & H-TAPS : YAV2CLTC10FX' or itemid='LUGS & H-TAPS : YAV2CL2TC14E2FX')) as qryresults
RESULT:
description
"LUG COMPRESSION # 2 AWG FLEX 1 HOLE #10 STUD STANDARD BARREL 0.68"" TONGUE WIDTH BROWN W/ INSP WNDW BURNDY"
"LUG COMPRESSION # 2 AWG FLEX 2 HOLE 1/4"" STUD 3/4"" CENTER STANDARD BARREL 0.68"" TONGUE WIDTH BROWN W/ INSP WNDW BURNDY"
This query returns the same two results, but the "order by" is backwards:
select distinct
itemid, description
from
(select row_number() over (order by description) as rownum, itemid, description
from tbl_product
where (itemid='LUGS & H-TAPS : YAV2CLTC10FX' or itemid='LUGS & H-TAPS : YAV2CL2TC14E2FX')) as qryresults
RESULT:
itemid description
LUGS & H-TAPS : YAV2CL2TC14E2FX "LUG COMPRESSION # 2 AWG FLEX 2 HOLE 1/4"" STUD 3/4"" CENTER STANDARD BARREL 0.68"" TONGUE WIDTH BROWN W/ INSP WNDW BURNDY"
LUGS & H-TAPS : YAV2CLTC10FX "LUG COMPRESSION # 2 AWG FLEX 1 HOLE #10 STUD STANDARD BARREL 0.68"" TONGUE WIDTH BROWN W/ INSP WNDW BURNDY"
The only difference between the two is the "itemid" column present in the "select" clause (twice). I'm not understanding why selecting that column should cause the sorting to be backwards.
Thank you!
ASKER
I thought "order by description" would determine the order. I don't have "order by itemid, description". How can I modify the second query so that it will return both itemid and description, but be sorted on description?
The ORDER BY in a ROW_NUMBER clause is used to assign values, it does not necessarily control the order in which rows are returned from the query.
An inner query order is never a guarantee of the order of rows for an outer query.
In short, if you want a specific order of rows returned by a query, you must ORDER BY in the outer query, such as:
select distinct
itemid, description
from
(select row_number() over (order by description) as rownum, itemid, description
from tbl_product
where (itemid='LUGS & H-TAPS : YAV2CLTC10FX' or itemid='LUGS & H-TAPS : YAV2CL2TC14E2FX')) as qryresults
order by description, itemid
Or even:
select distinct
description
from
(select row_number() over (order by description) as rownum, description
from tbl_product
where (itemid='LUGS & H-TAPS : YAV2CLTC10FX' or itemid='LUGS & H-TAPS : YAV2CL2TC14E2FX')) as qryresults
order by description
The distinct caused SQL to do a sort, which is why the rows were in order, but that was incidental/accidental, since an order by clause was not used.
An inner query order is never a guarantee of the order of rows for an outer query.
In short, if you want a specific order of rows returned by a query, you must ORDER BY in the outer query, such as:
select distinct
itemid, description
from
(select row_number() over (order by description) as rownum, itemid, description
from tbl_product
where (itemid='LUGS & H-TAPS : YAV2CLTC10FX' or itemid='LUGS & H-TAPS : YAV2CL2TC14E2FX')) as qryresults
order by description, itemid
Or even:
select distinct
description
from
(select row_number() over (order by description) as rownum, description
from tbl_product
where (itemid='LUGS & H-TAPS : YAV2CLTC10FX' or itemid='LUGS & H-TAPS : YAV2CL2TC14E2FX')) as qryresults
order by description
The distinct caused SQL to do a sort, which is why the rows were in order, but that was incidental/accidental, since an order by clause was not used.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I guess I didn't understand that before. It always seemed to work in the past, maybe I just got lucky. Anyway, thanks for your help!
ASKER
I am using row_num in my final query, I was just trying to give you a stripped down version of my more complicated working query.
Great that you got it !! Excellent.
ASKER
Sorry, I thought I had already assigned points.
Interesting points assignment. Good luck with future qs.
ASKER
Oh geez, I apologize! I did not realize there was more than one respondent. Somehow missed that.
Oh, ok, that makes sense. Now maybe it's just me, but I thought my comments were more useful and on point (of course, maybe that's just me favoring my own answer :-) ).
[To be clear, I am not suggesting he just copied my answer, as the times are extremely close, and cross-posting is unavoidable on a site like this.]
[To be clear, I am not suggesting he just copied my answer, as the times are extremely close, and cross-posting is unavoidable on a site like this.]
Thats why. , DISTINCT will give you distinct records, order will be decided on the columns selected.
Innner row_number() -- does not matter.