Custom order in SQL

Question

We query the database for data as follows

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

The result that I received from the request is in the following order: 1,2,6,33,78.

I want to get the result in the same order (6.33.1.78.2). Is there a way to get the data in the same order.

EDIT * I am using SQL 2008 *

Answer 1

add this order as suggested

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 you use MySQL, you can do it

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

Answer 2

Using the constructor of table values :

 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 

Answer 3

I don't know the background, but usually I achieve this custom order in an extra orderIndex column. This way I can manually control the order when pasting into a table and add this column to the default ORDER BY clause of queries

Answer 4

If you are using SQL Server, you can use charindex .

 select A, B from Table1 where ID in (6,33,1,78,2) order by charindex(','+cast(ID as varchar(10))+',', ',6,33,1,78,2,') 

Answer 5

ugly solution:

 select a,b,...f from table1 where id in 6 UNION select a,b,...f from table1 where id in 33 and so on.. 

"best" solution: add another column to your query and make case 6, then 0, case 33 then 1, etc.

 select a,b,...f , case id when 6 then 0 when 33 then 1 <and so on> end from table1 where ... 

and then sort by this new column