CODEWITHSUNDEEP
sqlsql-server
JohnS
JohnS

Reputation: 89

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

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.

Upvotes: 9

Views: 10796

Answers (4)

Wesley.GONG
Wesley.GONG

Reputation: 1

First, create a string split function in your sqlserver:

CREATE FUNCTION [fn_split](@text NVARCHAR(2000), @delimiter NCHAR(1))
    RETURNS @retable TABLE([s_key] NVARCHAR(64))
AS
BEGIN
    DECLARE @index INT;
    SET @index = -1;
    WHILE (LEN(@text) > 0)
    BEGIN
        SET @index = CHARINDEX(@delimiter, @text);
        IF (@index > 1)
            BEGIN
                INSERT INTO @retable VALUES(LEFT(@text, @index - 1));
                SET @text = RIGHT(@text, (LEN(@text) - @index));
            END
        ELSE
            BEGIN
                INSERT INTO @retable VALUES(@text);
                BREAK;
            END
    END
    RETURN;
END

Second, using sql query like this:

DECLARE @ids NVARCHAR(200)='5,64,2,8,7,1';
SELECT * FROM [table] a INNER JOIN (SELECT [s_key] FROM [fn_split](@ids, ',')) b ON [b].[s_key] = a.[column_id];

Upvotes: 0

Ullas
Ullas

Reputation: 11556

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];

Upvotes: 12

Nir Kornfeld
Nir Kornfeld

Reputation: 827

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.

Upvotes: 1

DVT
DVT

Reputation: 3127

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;

Upvotes: 2

Related Questions