sqlsql-serversql-order-bysql-convert

Sort by year as nvarchar and then numerically

I have an nvarchar column that in essence stores the following data:

20-198
99-135
19-135
20-197
20-195
99-435

The first two numbers represent the year created and the last numbers represent the id of the unit made that year. Whenever I sort I get the 99's first and 19 last. I'd like to sort by latest year first and then numerically.

I have tried converting to datetime and using sub-strings but I cannot create a valid query and I'm not really sure where to go from here.

Any help would be appreciated!

Solution

  • You can use conditional logic in the order by:

    order by (case when col <= '21' then 1 else 2 end),
         col desc