SQL Server: custom column sort in WHERE IN

Question

I want to sort the results of this query in SQL Server:

SELECT * 
FROM t1 
WHERE t1.my_field_name IN ('val1', 'val2', 'val3');

...so that I get the results sorted in the same order as the IN list values.

In mySQL this can be done easily:

SELECT * 
FROM t1
WHERE my_field_name IN ('val1', 'val2', 'val3') 
ORDER BY FIELD(my_field_name, 'val1', 'val2', 'val3');

...but I don't know how to do it in SQL Server. Any suggestion?

Answer 1

Use a case statement like this:

ORDER BY
   CASE field
      WHEN 'val1' THEN 1
      WHEN 'val2' THEN 2
      WHEN 'val3' THEN 3
   END, my_field_name

Answer 2

The easiest way to do this is to join on a VALUES virtual table, along with an ordinal to sort by:

SELECT
  t1.*
FROM t1 
JOIN (VALUES
    ('val1', 1),
    ('val2', 2),
    ('val3', 3)
) v(value, ord) ON t1.my_field_name = v.value
ORDER BY
  v.ord;

In the event that there are duplicate values (eg if you were passing in parameters) you would need to aggregate them first.