romeiovasu
asked on
sql query sorting
Hi All,
i have a customerid field, 1,2,3,20,30,31,32,100,150, 250
but when we sort it comes 1,100,150,2,20,250,30,31,3 2
is there any way we can sort incremented numbers using sql query
i have a customerid field, 1,2,3,20,30,31,32,100,150,
but when we sort it comes 1,100,150,2,20,250,30,31,3
is there any way we can sort incremented numbers using sql query
ASKER
it has got character also inside
example c01, c02, c03, c20, C25
example c01, c02, c03, c20, C25
It sounds like your column is a varchar column, not an integer or decimal column. If you can change the column type, you should be okay. Otherwise you may have to CAST the column value before you sort on it.
(Nevermind - I see [Jim Horn] beat me)
(Nevermind - I see [Jim Horn] beat me)
You can also use Format() in your order By clause.
Say, the maximum charters is 5,
Order By FORMAT(FieldName, ‘00000’)
Say, the maximum charters is 5,
Order By FORMAT(FieldName, ‘00000’)
>it has got character also inside
Just a thought: That's relevant to your question, so details like this should be included in the question.
Is it always the left-most single character? If yes, you can remove it..
Although you may have to deal with other issues such as blanks or NULL values, more than one letter, or other letters, based on if the data is as advertised here.
Just a thought: That's relevant to your question, so details like this should be included in the question.
Is it always the left-most single character? If yes, you can remove it..
SELECT value
FROM your_table
ORDER BY CAST(RIGHT(value, LEN(value) - 1) as int)
Although you may have to deal with other issues such as blanks or NULL values, more than one letter, or other letters, based on if the data is as advertised here.
ASKER
i have values like this C0010001F
>i have values like this C0010001F
Before we go further, instead of a 'multiple iterations of ten words or less' approach, how about providing us a data mockup of the data you're working with.
Before we go further, instead of a 'multiple iterations of ten words or less' approach, how about providing us a data mockup of the data you're working with.
Obviously all those values can't mean the same thing. Should they all be in the same column?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What you describe is typical of character sorting, so to sort numerically you can CAST(that number as numeric(19,4))
This also assumes that every value is a number, otherwise you'd have to weed them out.