SQL WHERE IN (...) sort in list order?

Question

Say I have a database query with a where clause

WHERE _id IN (5,6,424,2) 

Is it possible to sort the sorted return cursor in the order specified in the _id list in the list? _id from first to last in Cursor to be 5, 6, 424, 2?

This happens on Android via ContentProvider, but it is probably not relevant.

Answer 1

Select an ID list using a subquery and join it:

 select t1.* from t1 inner join ( select 1 as id, 1 as num union all select 5, 2 union all select 3, 3 ) ids on t1.id = ids.id order by ids.num 

UPD: Fixed code

Answer 2

One approach could be a separate SQL query with UNION between each. You would obviously issue each request in the order in which you would like to return it.

Answer 3

... order using the case when _id = 5, then 1
when _id = 6 then 2
end

and etc.

Answer 4

You can connect to a virtual table containing the list needed in sort order

 select tbl.* from tbl inner join ( select 1 as sorter, 5 as value union all select 2, 6 union all select 3, 424 union all select 4, 2) X on tbl._id = X.value ORDER BY X.sorter 

Answer 5

List? You have no list !;)

It:

 WHERE _id IN (5,6,424,2) 

is just syntactic sugar for this:

 WHERE ( _id = 5 OR _id = 6 OR _id = 424 OR _id = 2 ) 

SQL has only one data structure, which is a table. Your (5,6,424,2) also not a table! :)

You can create a table of values, but the next problem is that the tables do not have a logical order. Therefore, according to @cyberkiwi's answer, you will need to create a column explicitly to simulate the sort order. And to make this explicit to the calling application, make sure you expand this column in the SELECT your query.