Sort by year as nvarchar and then numerically

Question

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

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!

Seems like you need to fix your design and split the value into 2 numerical data type columns. Normalise your data and you don't have the problem youre having. — Thom A, Aug 05 '20 at 19:35
you should edit to show the desired sort order - then show the sql you tried. this would be easier with a better design - but you can be successful with substring manipulation — Randy, Aug 05 '20 at 22:45

score 1 · Accepted Answer · answered Aug 05 '20 at 19:35

1

You can use conditional logic in the order by:

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

answered Aug 05 '20 at 19:35

Gordon Linoff

1,242,037
58
646
786

This is perfect! Thank you – Corey Franco Aug 05 '20 at 19:54
1

I'd use `< '70'`, just for tradition's sake :) – MatBailie Aug 05 '20 at 20:06

Sort by year as nvarchar and then numerically

1 Answers1