How to order the results of a query by the same order as the IN statement ?

Question

Hello

I have the following query (I'm using mysql)

SELECT * FROM campaigns where campaign_id IN ( 'idStrOne', 'idStrTwo', 'idStrThree' );

The results of which are being ordered by the primary key of the table campaigns which is 'id'. This is not the order I want.

I want the results to come back in the same order as the arguments to the IN function. So in this case the order I want is

idStrOne, idStrTwo, idStrThree

How do I get this order ?

Answer 1

You could try adding a CASE expression in the ORDER BY Clause

SELECT * 
FROM campaigns 
WHERE campaign_id IN ( 'idStrOne', 'idStrTwo', 'idStrThree' )
ORDER BY 
(CASE campaign_id WHEN 'idStrOne' THEN 1 WHEN 'idStrTwo' THEN 2 ELSE 3 END);

Answer 2

Duplicated See here: http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause