Non-sequential sorting

Transact-SQL
1

So let's say that I have a table such as the following:

ID	Client	Type
1	Johnson	  3
2	Smith	  1
3	Doe	      2

Is there a way to write a Select query to sort non-sequentially a such way that all records with type "2" are prioritized, then type "1" and then finally type "3"? Meaning that in the example above, the Select query would return

ID	Client	Type
3	Doe	      2
2	Smith	  1
1	Johnson	  3

Thanks!

2

Generally, you can use conditions in the ORDER BY:

ORDER BY CASE WHEN Type = 2 THEN 0 WHEN Type = 1 THEN 1 ELSE 3 END

There are times when SQL won't recognize conditions in the ORDER BY, then you can use a CROSS APPLY to create the sort condition:


SELECT ...
FROM ...
CROSS APPLY (
    SELECT CASE WHEN Type = 2 THEN 0 WHEN Type = 1 THEN 1 ELSE 2 END AS sort1
) AS ca1
ORDER BY sort1
3

Thanks Scott !