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 [https://stackoverflow.com/questions/119730/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?

CodePudding user response：

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;