SQL Server Select query with IN() and order by the same

Question

I need a SELECT query with an IN clause, as well as Order by:

select * 
from table 
where column_id IN (5,64,2,8,7,1) 

This code returns 1, 2, 5, 7, 8, 64.

Now I need to return the same select in order

Output needs to be: 5, 64, 2, 8, 7, 1

In Mysql, field option is there, but SQL Server does not have such a field option.

Answer 1

Use the values you want to search in a Table Valued Constructor and also give a row number and then join it with your table and then order it according to the values in the table valued constructor.

Query

SELECT t1.* 
FROM(VALUES(1, 5), (2, 64), (3, 2), (4, 8), (5, 7), (6, 1)) t2([rn], [id])
JOIN [your_table_name] t1
ON t1.[id] = t2.[id]
ORDER BY t2.[rn];

Also you can create a table variable with the values you want to search and also an identity column in that. And then join it with your table.

Query

DECLARE @tbl AS TABLE([rn] INT IDENTITY(1, 1), [id] INT);
INSERT INTO @tbl([id]) VALUES(5), (64), (2), (8), (7), (1);

SELECT t1.*
FROM [your_table_name] t1
JOIN @tbl t2
ON t1.[id] = t2.[id]
ORDER BY t2.[rn];

Answer 2

In SQL-Server, when you want to order by something, you have to specifically spell it out.

Try this

select * from table where column_id IN (5,64,2,8,7,1)
order by
case column_id
    when 5 then 1
    when 64 then 2
    when 2 then 3
    when 8 then 4
    when 7 then 5
    when 1 then 6
    else 10
end;

Answer 3

It is a bit complicated, but you can do this:

WITH inner_tbl (key, orderId) AS
( 
SELECT key, ROW_NUMBER() OVER(ORDER BY SELECT 1)
FROM (VALUES (5),(64),(2),(8),(7),(1) ) d
)
SELECT table.* FROM table 
INNER JOIN inner_tbl ON table.column_id=inner_tbl.key
ORDER BY inner_tbl.orderId

The ROW_NUMBER function will create the order column you need.