Click here to Skip to main content
15,662,823 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
10TH Grade
11TH Grade
12TH Grade
1ST Grade
2ND Grade
3RD Grade
4TH Grade
5TH Grade
6TH Grade
7TH Grade
8TH Grade
9TH Grade

I have above data in my sql table, I want to sort them in ascending order, how to do it ?
Updated 25-Nov-15 19:50pm

If you have 'VARCHAR' datatype in sql then it will take 1, 10, 11 as ascending order, you can use following query to resolve your issue
select col from yourtable order by length(col),col
Share this answer
Member 11751610 26-Nov-15 1:58am    
Thank you koolprasad2003
With a lot of difficulty, if that's what you are storing.
String sorting is character based, and the whole comparison depends on the first different character - so the sort order will not be numeric:
In your case it's even worse, because the "TH", "ST", "ND" suffixes are counted in the comparison as well.

What I would do is set up a separate table
Grade Description
(int) (NVARCHAR(20))
1     1ST Grade
2     2ND Grade
3     3RD Grade
4     4TH Grade
5     5TH Grade
6     6TH Grade
7     7TH Grade
8     8TH Grade
9     9TH Grade
10    10TH Grade
11    11TH Grade
12    12TH Grade
99    Advanced
199   Mains
And store the Grade value with your other data, sort by that, and use a JOIN to retrieve the Description.

Any other method is going to be clumsy and error prone.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900