SQL WHERE IN (...) sort by order of the list?

Let's say I have query a database with a where clause

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

Is there any way for the returned cursor to be sorted in the order that the _id's where listed in the list? _id attribute from first to last in Cursor to be 5, 6, 424, 2?

This happen开发者_StackOverflow社区s to be on Android through a ContentProvider, but that's probably not relevant.

Select ID list using subquery and join with 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: Code fixed

One approach might be to do separate SQL queries with a UNION between each. You would obviously issue each query in the order you would like it returned to you.

... order by
case when _id=5 then 1
when _id=6 then 2
end

etc.

You can join it to a virtual table that contains the list required 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

List? You don't have a list! ;)

This:

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

is mere syntactic sugar for this:

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

SQL has but one data structure, being the table. Your (5,6,424,2) isn't a table either! :)

You could create a table of values but your next problem is that tables do not have any logical ordering. Therefore, as per @cyberkiwi's answer, you'd have to create a column explicitly to model the sort order. And in order to make it explicit to the calling application, ensure you expose this column in the SELECT clause of your query.