Avatar of 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,32

is there any way we can sort incremented numbers using sql query
Microsoft SQL Server

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
Jim Horn

Is your field a character (char, varchar, nvarchar) or numeric (int, money, numeric)?
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.

it has got character also inside
example c01, c02, c03, c20, C25
Paul MacDonald

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)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mike Eghtebas

You can also use Format() in your order By clause.

Say, the maximum charters is 5,

Order By FORMAT(FieldName, ‘00000’)
Jim Horn

>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..

SELECT value
FROM your_table
ORDER BY CAST(RIGHT(value, LEN(value) - 1)  as int)

Open in new window

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.

i have values like this C0010001F
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jim Horn

>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.
Paul MacDonald

Obviously all those values can't mean the same thing.  Should they all be in the same column?
Scott Pletcher

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.