How to sort T-SQL query output in a custom order?

Question

I am using SQL Server 2014. I need the output of my T-SQL query to be sorted in a specific order.

I have tried the following but it is not working as I intended. Here is an extract of the last part of my T-SQL query:

...  
GROUP BY xx.[Market]

ORDER BY CASE
WHEN xx.[Market] = 'France' THEN '1'
WHEN xx.[Market] = 'United Kingdom' THEN '2'
WHEN xx.[Market] = 'Germany' THEN '3'
WHEN xx.[Market] = 'Belgium' THEN '4'
WHEN xx.[Market] = 'Reunion' THEN '5'
WHEN xx.[Market] = 'South Africa' THEN '6'
WHEN xx.[Market] = 'Russia' THEN '7'
WHEN xx.[Market] = 'Middle East' THEN '8'
WHEN xx.[Market] = 'Central Europe' THEN '9'
WHEN xx.[Market] = 'Poland' THEN '10'
WHEN xx.[Market] = 'Scandinavia' THEN '11'
WHEN xx.[Market] = 'Netherlands' THEN '12'
WHEN xx.[Market] = 'India' THEN '13'
WHEN xx.[Market] = 'G&I' THEN '14'
WHEN xx.[Market] = 'China' THEN '15'
WHEN xx.[Market] = 'OTA' THEN '16'
WHEN xx.[Market] = 'Web Direct' THEN '17'
WHEN xx.[Market] = 'DB Local' THEN '18'
WHEN xx.[Market] = 'DB International' THEN '19'
WHEN xx.[Market] = 'Other Markets' THEN '20'

ELSE xx.[Market] END

However, I am getting the following results:

Market               RN
France               196
Scandinavia           80
Netherlands          320
India                  2
OTA                  372
Web Direct           140
DB Local              11
DB International      25
United Kingdom     2,424
Other Markets        116
Germany              609
Belgium              350
Reunion               27
South Africa          42
Russia                 7
Central Europe        17
Switzerland           34

Is it because some markets are not included in the output? If yes, how do I handle this issue (as there will be cases where markets which are missing in this output might be present when I will use my query with different filters)?

Usually by about the tenth WHEN, you should be wondering whether it's more sensible to be modelling this as an DisplayOrder column (either in an existing table or a new one just for this purpose). — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 3, 2018 at 6:45
@Damien_The_Unbeliever Well, if you have to actually write the tenth before that idea pops to mind, you should probably have more coffee before resuming work :-) — Zohar Peled
– Zohar Peled, Commented Oct 3, 2018 at 9:06

Jacek Wróbel · Accepted Answer · 2018-10-03 06:00:35Z

6

Instead of using string values in CASE clause use numeric values.

ORDER BY CASE
WHEN xx.[Market] = 'France' THEN 1
WHEN xx.[Market] = 'United Kingdom' THEN 2
...
ELSE 1000
END

answered Oct 3, 2018 at 6:00

Jacek Wróbel

1,23211 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to sort T-SQL query output in a custom order?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related