SQLite Order by WHERE IN clause [duplicate]
ORDER BY CASE id WHEN x THEN y
Found it in an obscure SQLite forum.
There's a neat way of ordering things that is very specific but lends itself to be programmatically generated.
To achieve the desired order, you can use the following:
SELECT * FROM todosWHERE todos.id in ( 1, 3, 2, 4 ) ORDER BY CASE todos.id WHEN 1 THEN 1 WHEN 3 THEN 2 WHEN 2 THEN 3 WHEN 4 THEN 4 ENDThis returns the records in the following order:
1324As you can see, you're manually specifying the order of each record. This would be laborious if you were doing this manually, but this can be easily programmed and appended to a query, if you're using a programming language.
For example, I'm using this with Android/Java and ended up doing something like this to generate this ORDER BY clause:
String orderBy = "CASE todos.id ";int i = 1;for ( int id : ids ) { orderBy = orderBy.concat( " WHEN " + id + "' THEN " + i ); i++;}orderBy = orderBy.concat( " END");// Append `orderBy` to your normal SQLite query.