CODEWITHSUNDEEP
sqlsortingwhere-clause
cemulate
cemulate

Reputation: 2333

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 happens to be on Android through a ContentProvider, but that's probably not relevant.

Upvotes: 6

Views: 7727

Answers (5)

onedaywhen
onedaywhen

Reputation: 57073

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.

Upvotes: 1

oryol
oryol

Reputation: 5248

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

Upvotes: 4

RichardTheKiwi
RichardTheKiwi

Reputation: 107786

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

Upvotes: 1

lll
lll

Reputation: 317

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

etc.

Upvotes: 1

toddkitta
toddkitta

Reputation: 587

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.

Upvotes: 1

Related Questions