Sorting results of SQL query just like IN parameter list

Question

I'm doing a query which looks something like

SELECT id,name FROM table WHERE id IN (2,1,4,3)

I'd like to get

id name
2  B
1  A
4  D
3  C

but I'm getting

1 A
2 B
3 C
4 D

Is there any way to sort the query results in the same way as the list I'm including after IN?

Believe me, I have a practical reason that I would need it for ;)

what kind of db you are using? MySQL, oracle, MSSQL or other? — ah_hau, Sep 15 '14 at 08:49

ah_hau · Answer 1 · 2014-09-15T09:21:47.873

0

SELECT id,name FROM table WHERE id IN (2,1,4,3)
ORDER BY CASE id
  WHEN 2 THEN 1
  WHEN 1 THEN 2
  WHEN 4 THEN 3
  WHEN 3 THEN 4
  ELSE 5
END

This might solve your problem.

Solution 2, insert your list into a temp table and get them a running sequence

id, seq(+1 every new row added)
-----------------
2    1
1    2
4    3
3    4

then join 2 table together and order by this seq.

edited Sep 15 '14 at 09:21

answered Sep 15 '14 at 08:51

ah_hau

768
4
11

I don't have a constant list of IN parameters. It is generated by another part of my application and may consist of 1000 numbers or so. I guess, I can't use this solution in my case. – RRM Sep 15 '14 at 08:59
then you might try to do it on the coding side after get the list instead of sql – ah_hau Sep 15 '14 at 09:09
http://stackoverflow.com/questions/1244028/sql-order-by-list-of-strings, here they suggest 1.order by FIELD(your list), 2.order by find_in_set(id, 'your list') – ah_hau Sep 15 '14 at 09:19

score 0 · Answer 2 · answered Sep 15 '14 at 12:24

Okay, I did it myself. It's a bit mad but it works ;)

DECLARE @IDs varchar(max)
DECLARE @nr int
DECLARE @znak varchar(1)
DECLARE @index int
DECLARE @ID varchar(max)

SET @IDs='7002,7001,7004,7003'
SET @nr=1
SET @index=1

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp (nr int, id int)

--fill temp table with Ids
WHILE @index<=LEN(@Ids)
BEGIN
 set @znak=''
 set @ID=''

 WHILE @znak<>',' AND @index<=LEN(@Ids)
 BEGIN
  SET @znak= SUBSTRING(@IDs,@index,1)
  IF @znak<>',' SET @ID=@ID+@znak
  SET @index=@index+1 
 END
 INSERT INTO #temp(nr,id) VALUES (@nr,CAST(@ID as int))
 SET @nr=@nr+1
END

-- select proper data in wanted order
SELECT MyTable.* FROM MyTable
INNER JOIN #temp ON MyTable.id=#temp.id
ORDER BY #temp.nr

Sorting results of SQL query just like IN parameter list

2 Answers2