3

I have a column that has numbers from 1 to 12, and also can include letters W, E, S. I want the column to sort numerically (1,2,3,4...) then list all records with a letter.

So, before I did anything the column was ordering like:

1, 10, 11, 12, 2, 3, 4, 5 ... E, S, W

so I found this question in Stack overflow How do I sort a VARCHAR column in SQL server that contains numbers? I tried all of the answers and were receiving errors on all of the examples except for the following:

Order by (Total +0) ASC

Now the ordering is correct for the numbers (1, 2, 3...) but the issue I have now is that it shows all rows with letter values before all rows with numbers.

How do I get the database to display the letter values after all of the number values?

DevThiman
  • 920
  • 1
  • 9
  • 24
TEAM
  • 55
  • 4

1 Answers1

1

You can use a case expression to first split the results into 2 groups and order by that, then your existing condition:

order by case when Total + 0 > 0 then 0 else 1 end, Total + 0;
Stu
  • 30,392
  • 6
  • 14
  • 33