sqlite - 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 :)

Answer 1

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