SQL non alphabetical order in WHERE IN

Let’s say I have this query:

SELECT name
FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
                  'CE367FAACEX9ANPH-153877',
                  'NI564FAACJSFANPH-162605',
                  'GE526OTACCD3ANPH-149839')

the result is:

CE367FAACDHCANPH-151556
CE367FAACEX9ANPH-153877
GE526OTACCD3ANPH-149839
NI564FAACJSFANPH-162605

which is ordered by the alphabetical order

How can I get a result order by the index of appearance in the list?

basically I want this as a result:

CE367FAACDHCANPH-151556
CE367FAACEX9ANPH-153877
NI564FAACJSFANPH-162605
GE526OTACCD3ANPH-149839

Answer

This is quite a popular approach to sort things in SQL, so I’ve blogged about this example here. You would have to explicitly order by those values in your list, e.g. using a CASE expression:

SELECT name
FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
               'CE367FAACEX9ANPH-153877',
               'NI564FAACJSFANPH-162605',
               'GE526OTACCD3ANPH-149839')
ORDER BY CASE WHEN name = 'CE367FAACDHCANPH-151556' THEN 1
              WHEN name = 'CE367FAACEX9ANPH-153877' THEN 2
              WHEN name = 'NI564FAACJSFANPH-162605' THEN 3
              WHEN name = 'GE526OTACCD3ANPH-149839' THEN 4
         END

Example on SQLFiddle

If you want to avoid repeating those literals, you could resort to this trick:

SELECT product.name
FROM product
JOIN (
  VALUES('CE367FAACDHCANPH-151556', 1),
        ('CE367FAACEX9ANPH-153877', 2),
        ('NI564FAACJSFANPH-162605', 3),
        ('GE526OTACCD3ANPH-149839', 4)
) AS sort (name, sort)
ON product.name = sort.name
ORDER BY sort.sort

Example on SQLFiddle