• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

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
0
romeiovasu
Asked:
romeiovasu
  • 3
  • 2
  • 2
  • +2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
romeiovasuAuthor Commented:
it has got character also inside
example c01, c02, c03, c20, C25
0
 
Paul MacDonaldDirector, Information SystemsCommented:
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)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mike EghtebasDatabase and Application DeveloperCommented:
You can also use Format() in your order By clause.

Say, the maximum charters is 5,

Order By FORMAT(FieldName, ‘00000’)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
romeiovasuAuthor Commented:
i have values like this C0010001F
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Obviously all those values can't mean the same thing.  Should they all be in the same column?
0
 
Scott PletcherSenior DBACommented:
You can do something like this:

ORDER BY RIGHT(REPLICATE('0', 9) + customerid, 9)

Adjust the "9" to be the max length you'd ever expect -- extra length won't give you bad results, it will just add a few more bytes to the sort column value.  For example:

ORDER BY RIGHT(REPLICATE('0', 12) + customerid, 12)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now