Select records with order of IN clause

Question

I have

SELECT * FROM Table1 WHERE Col1 IN(4,2,6)

I want to select and return the records with the specified order which i indicate in the IN clause (first display record with Col1=4, Col1=2, ...)

I can use

SELECT * FROM Table1 WHERE Col1 = 4
UNION ALL
SELECT * FROM Table1 WHERE Col1 = 6 , .....

but I don't want to use that, cause I want to use it as a stored procedure and not auto generated.

Solution 1

You have a couple of options. Simplest may be to put the IN parameters (they are parameters, right) in a separate table in the order you receive them, and ORDER BY that table.

Solution 2

I know it's a bit late but the best way would be

SELECT *
FROM   Table1
WHERE  Col1 IN( 4, 2, 6 )
ORDER  BY CHARINDEX(CAST(Col1 AS VARCHAR), '4,2,67')

Or

SELECT CHARINDEX(CAST(Col1 AS VARCHAR), '4,2,67')s_order,
       *
FROM   Table1
WHERE  Col1 IN( 4, 2, 6 )
ORDER  BY s_order

Solution 3

The solution is along this line:

SELECT * FROM Table1 
WHERE Col1 IN(4,2,6)
ORDER BY 
    CASE Col1 
    WHEN 4 THEN 1
    WHEN 2 THEN 2
    WHEN 6 THEN 3
    END

Solution 4

select top 0 0 'in', 0 'order' into #i
insert into #i values(4,1)
insert into #i values(2,2)
insert into #i values(6,3)

select t.* from Table1 t inner join #i i on t.[in]=t.[col1] order by i.[order]

Solution 5

Replace the IN values with a table, including a column for sort order to used in the query (and be sure to expose the sort order to the calling application):

WITH OtherTable (Col1, sort_seq)
     AS 
     (
      SELECT Col1, sort_seq
        FROM (
              VALUES (4, 1), 
                     (2, 2), 
                     (6, 3)
             ) AS OtherTable (Col1, sort_seq)
     )
SELECT T1.Col1, O1.sort_seq
  FROM Table1 AS T1
       INNER JOIN OtherTable AS O1
          ON T1.Col1 = O1.Col1
 ORDER 
    BY sort_seq;

In your stored proc, rather than a CTE, split the values into table (a scratch base table, temp table, function that returns a table, etc) with the sort column populated as appropriate.

View more solutions