7

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JohnS
  • 69
  • 1
  • 7
  • Your question is unclear what you want to achieve. Please provide more details – Rahul Neekhra Jan 18 '17 at 05:33
  • 1
    You want the result to appear in the same order as the IN list. How is the IN list generated? from a list? There is no simple answer to this. You need to actually load the in list into a table with an additional order by column. – Nick.Mc Jan 18 '17 at 05:35
  • I have to **select * from table where column_id IN (5,64,2,8,7,1)** it return default column_id Asc. Now need to change the Order of listing. – JohnS Jan 18 '17 at 05:35
  • Can you post a reference to the MySQL option you're talking about? To order the results from a table you need an `ORDER BY`. So what field are you going to order by? Where does the in list come from? Do you type it in? There is no native way to do what you want. – Nick.Mc Jan 18 '17 at 05:37
  • @Nick.McDermaid Your Correct Need to order by select IN list – JohnS Jan 18 '17 at 05:38
  • @Nick.McDermaid [link](http://stackoverflow.com/questions/2185029/sort-by-order-of-values-in-a-select-statement-in-clause-in-mysql) – JohnS Jan 18 '17 at 05:39
  • OK. There's no similar function in SQL Server. @DVT answer or a variation on that is what you need to do. That's why I asked where the in list came from - because you need to put some smarts in – Nick.Mc Jan 18 '17 at 05:42
  • possible duplicate: http://stackoverflow.com/questions/1831799/ordering-query-result-by-list-of-values – swe Jan 18 '17 at 07:25
  • duplicate of https://stackoverflow.com/q/396748/253096 – TimoSolo Jan 23 '20 at 09:54

4 Answers4

10

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];
Ullas
  • 11,450
  • 4
  • 33
  • 50
1

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;
DVT
  • 3,014
  • 1
  • 13
  • 19
1

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.

Nir Kornfeld
  • 827
  • 7
  • 11
  • 1
    This isn't guaranteed. Why not just add the OrderId to the values clause explicitly rather than relying on a non deterministic row number that can be optimised out. – Martin Smith Jan 18 '17 at 06:11
  • Of course adding the order will be more absolute, but will also complicate the query. From my tests, row_number() over(order by select 1) will keep the order – Nir Kornfeld Jan 18 '17 at 06:13
  • 1
    There's a difference between "happening in your tests" ( which just shows it works at least some of the time) and "being documented as guaranteed to always happen" – Martin Smith Jan 18 '17 at 06:15
0

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];
Wesley.GONG
  • 1