How do I preserve the order of a SQL query using the IN command

Question

SELECT * FROM tblItems
WHERE itemId IN (9,1,4)

Returns in the order that SQL finds them in (which happens to be 1, 4, 9) however, I want them returned in the order that I specified in the array.

I know I could reorder them after in my native language (obj c), but is there a neat way to do this in SQL?

Somthing like this would be great:

ORDER BY itemId (9,1,4) --    <-- this dosn't work :)

http://stackoverflow.com/questions/866465/sql-order-by-the-in-value-list — Atomsk, Mar 01 '13 at 10:56

score 6 · Answer 1 · answered Mar 01 '13 at 10:54

Probably the best way to do this is create a table of item IDs, which also includes a rank order. Then you can join and sort by the rank order.

Create a table like this:

 itemID rank
 9      1
 1      2
 4      3

Then your query would look like this:

select tblItems.* from tblItems
    inner join items_to_get on
        items_to_get.itemID = tblItems.itemID
    order by rank

score 3 · Answer 2 · answered Mar 01 '13 at 11:02

3

Use a CASE expression to map the ID values to an increasing sequence:

... ORDER BY CASE itemId
             WHEN 9 THEN 1
             WHEN 1 THEN 2
             ELSE        3
             END

answered Mar 01 '13 at 11:02

CL.

173,858
17
217
259

Thanks - this seems to work! Is it any efficiency concerns as this scales? – Robert Mar 01 '13 at 11:25
You will make SQLite complain about a too-long command before execution speed gets noticeable. Anyway, the `... IN (...)` would have the same problem. If your list of IDs gets too long to include verbatim in the command string, you have to replace both `IN` and `ORDER BY` with dan1111's solution. – CL. Mar 01 '13 at 12:56

score 2 · Answer 3 · answered Mar 01 '13 at 10:54

2

I had the same task once in a mysql environment.

I ended up using

ORDER BY FIND_IN_SET(itemID, '9,1,4')

this is working for me since then. I hope it also works for sqlite

answered Mar 01 '13 at 10:54

DKSan

4,187
3
25
35

score 0 · Answer 4 · answered Mar 01 '13 at 10:53

0

You can add a case construct to your select clause.

select case when itemid = 9 then 1
when itemid = 1 then 2 else 3 end sortfield
etc
order by sortfield

answered Mar 01 '13 at 10:53

Dan Bracuk

20,699
4
26
43

score 0 · Answer 5 · answered Mar 01 '13 at 10:53

You could create a procedure to order the data in SQL, but that would be much more complicated than its native language counterpart.

There's no "neat way" to resort the data like that in SQL -- the WHERE clause of a SELECT simply says "if these criteria are matched, include the row"; it's not (and it cannot be) an ordering criterion.

How do I preserve the order of a SQL query using the IN command

5 Answers5

Linked

Related