SQL ORDER BY – how to honour same order as list in WHEN clause

A very basic SQL question I am forgetting.

Given

select * from customers
where customer_id in ('29383', '49405', '47483', '10209','46383', '93838');

…how can I order the results, such that it orders them in the same order as the in('... clause?

for example, rather than order by customer_id desc; I want the results to be:

29383
49405
47483
10209
46383
93838

Answer

On SQL Server, you may order using a CASE expression:

SELECT *
FROM customers
WHERE customer_id in ('29383', '49405', '47483', '10209','46383', '93838')
ORDER BY CASE customer_id
         WHEN '29383' THEN 1
         WHEN '49405' THEN 2
         WHEN '47483' THEN 3
         WHEN '10209' THEN 4
         WHEN '46383' THEN 5
         WHEN '93838' THEN 6 END;

Note that if you have a persistent need for this ordering, it might make more sense to keep this customer_id values and their mappings/orderings in a separate table. Then, join to this table and use the ordering value in your ORDER BY clause.