Custom order in SQL

Question

We are querying database to retrieve data in following fashion

select a,b,...f from table1 where id in (6,33,1,78,2)

The result I got from query is in following order 1,2,6,33,78.

I want the result in same order (6,33,1,78,2). Is there any way to retrieve the data in same order.

EDIT *I am using SQL 2008*

Answer 1

add this order by clause

order by case 
         when id = 6 then 1 
         when id = 33 then 2  
         when id = 1 then 3  
         when id = 78 then 4  
         when id = 2 then 5  
      end

If using MySQL you can do this

ORDER BY FIND_IN_SET(id, '6,33,1,78,2')

Answer 2

Using a Table Value Constructor:

SELECT a, b, ... f 
FROM 
        table1 
    JOIN
        ( VALUES 
            (1,  6), 
            (2, 33), 
            (3,  1), 
            (4, 78), 
            (5,  2) 
        ) AS ordering (position, id)
      ON  ordering.id = table1.id
ORDER BY position