CODEWITHSUNDEEP
sql-server
priyanka.sarkar
priyanka.sarkar

Reputation: 26498

How to do sorting in SQL SERVER varchar types

I have a table say tblSwap with a column StroreVals.

StroreVals

20

21

29

11

10

5

7

I want to get the result in descending order using SQL SERVER 2005.

How to do?

If you fire SELECT StroreVals FROM tblSwap ORDER BY StroreVals DESC

The output is 7

5

29

21

20

11

10

Also, if it is like "Abc", "Def","Azy","kly"? Please help

Upvotes: 0

Views: 1008

Answers (3)

kristof
kristof

Reputation: 53834

If you store your data as varchar it is by default sorted as varchar with the sorting order specified by the collation settings

when you have string: '3','111','2' and you sort desc you will get '3','2','111' in the same way as if you had strings 'c','aaa','b' sort desc as 'c','b','aaa'

If your field stores numbers only then store them as numbers or use the casting as suggested by tekBlues

If you have both numbers and strings and are not happy with the default sorting behaviour for strings you may need to define your own sorting criteria e.g. solution suggested by Mladen

Upvotes: 0

tekBlues
tekBlues

Reputation: 5793

You have stored the numbers in a varchar field, but you want them to be ordered like numbers.

Do this, convert the field to a numeric one for the sort:

SELECT StroreVals  
FROM tblSwap ORDER BY convert(int,StroreVals) DESC

Assumptions: all the values can be converted to int, otherwise, you'll get an error.

Upvotes: 5

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

if casting isn't an option you can do this:

SELECT StroreVals 
FROM tblSwap 
order by right(replicate('0', 11) + StroreVals, 10)

Upvotes: 3

Related Questions