How to keep SQL query order using IN command

Question

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

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

I know that I can change the order of their work in my native language (obj c), but is there a way to do this in SQL?

Something 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 to create a table of item identifiers, which also includes a ranking order. Then you can join and sort by rank order.

Create the table as follows:

  itemID rank 9 1 1 2 4 3 

Then your request will look like this:

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

Answer 2

Use the CASE expression to match identifier values ​​with an increasing sequence:

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

Answer 3

You can add a case construct to the select clause.

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

Answer 4

I had the same task in mysql environment.

I ended up using

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

it has been working for me since then. I hope it also works for sqlite

Answer 5

You can create a procedure for ordering data in SQL, but it will be much more complicated than its native language.

There is no "neat way" to use such data in SQL - the WHERE SELECT WHERE simply says: "if these criteria match, include the string"; it is not (and it cannot be) a criterion for ordering.