Getting a numbered list to come out of ORDER BY just right -- ANSWERED THANK YOU
-
Hello folks, I have this one field, which is MyCode let's just call it, and this field is an INT which can have any value from 1 to 10 inclusive. I am so frustrated because when I pass this column into an ORDER BY, it does 1 10 2 3 4 5 6 7 8 9 instead of 1 2 3 4 5 6 7 8 9 10 which is what one would want. Basically, I am wondering if there is anything else I can put in my query to teach SQL server the exact order I want my values to be sorted, without having to make a new sort order definitions file.
Sincerely Yours, Brian Hart
modified on Wednesday, February 9, 2011 4:00 PM
-
Hello folks, I have this one field, which is MyCode let's just call it, and this field is an INT which can have any value from 1 to 10 inclusive. I am so frustrated because when I pass this column into an ORDER BY, it does 1 10 2 3 4 5 6 7 8 9 instead of 1 2 3 4 5 6 7 8 9 10 which is what one would want. Basically, I am wondering if there is anything else I can put in my query to teach SQL server the exact order I want my values to be sorted, without having to make a new sort order definitions file.
Sincerely Yours, Brian Hart
modified on Wednesday, February 9, 2011 4:00 PM
Seems like the column is defined as some sort of character type. Do a "sp_help myTable" to get the detailed column definition. (Substitute you actual table name for myTable and don't use the quotes when trying to execute the command). David
-
Seems like the column is defined as some sort of character type. Do a "sp_help myTable" to get the detailed column definition. (Substitute you actual table name for myTable and don't use the quotes when trying to execute the command). David
Yes it is a varchar type. How interesting. I did a
CONVERT(INT, myField)in theSELECTlist and that cured it :) I am happy now!Sincerely Yours, Brian Hart
-
Hello folks, I have this one field, which is MyCode let's just call it, and this field is an INT which can have any value from 1 to 10 inclusive. I am so frustrated because when I pass this column into an ORDER BY, it does 1 10 2 3 4 5 6 7 8 9 instead of 1 2 3 4 5 6 7 8 9 10 which is what one would want. Basically, I am wondering if there is anything else I can put in my query to teach SQL server the exact order I want my values to be sorted, without having to make a new sort order definitions file.
Sincerely Yours, Brian Hart
modified on Wednesday, February 9, 2011 4:00 PM
It seems that the field may not be an INT, considering the results from the ORDER BY. If you are unable to change the defintion, you might try the following
order by length(MyCode), MyCode
:)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
It seems that the field may not be an INT, considering the results from the ORDER BY. If you are unable to change the defintion, you might try the following
order by length(MyCode), MyCode
:)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
The question's been answered. It was a varchar, i did a CONVERT(INT, myField) in the SELECT Thanks though!
Sincerely Yours, Brian Hart
