CODEWITHSUNDEEP
sqlitecursorsql-order-by
dor506
dor506

Reputation: 5404

Query Sqlite Database by specific/custom ordering?

Let's say I got a table, something like this:

ID | TITLE
1 | AAA
2 | BBB
3 | CCC
4 | DDD
5 | EEE
...
...

I want to perform a query, using IN opeator, while preserving the order of the IN Arguments

database.query("some_table", null, ID + " IN("+ idsStr+")", null, null, null, null);

For example, if the query is "select from some_table where id in (4,1,5) ...(order by???)", I want that the returned cursor to be sorted as 4,1,5

Is it possible? how?

Upvotes: 3

Views: 943

Answers (3)

Thomas Baruchel
Thomas Baruchel

Reputation: 7517

A second way of doing it (the first one being with CASE WHEN ... THEN END as already stated in other answers) is:

ORDER BY ID=4 DESC,
         ID=1 DESC,
         ID=5 DESC

Upvotes: 0

mclhrn
mclhrn

Reputation: 145

I haven't tested user3249477 answer above, but assuming the SQL is correct (it looks okay), then SQLiteDatabase has a rawquery method that returns a cursor.

database.rawquery("SELECT * FROM books WHERE _id IN(4, 1, 5) ORDER BY CASE _id WHEN '4' THEN 1 WHEN '1' THEN 2 WHEN '5' THEN 3");

Upvotes: 0

Simas
Simas

Reputation: 44118

EDIT:

Tested, works:

SELECT * FROM books WHERE _id IN(4, 1, 5) 
ORDER BY
CASE _id
WHEN '4' THEN 1
WHEN '1' THEN 2
WHEN '5' THEN 3
ELSE 4
END, _id;

Upvotes: 6

Related Questions