SQLite Order by WHERE IN clause

Question

I'm using SQLite and I'm providing a list of id values to retrieve. However, the order of those id values is important and I want to retrieve the records in the same order.

For example,

SELECT 
  * 
FROM 
  todos
WHERE 
  todos.id in ( 1, 3, 2, 4 ) 

This returns:

1
2
3
4

But I want it to return in the same order as the provided id values, like this:

1
3
2
4

I've seen answers for MySQL and PostgreSQL but not for SQLite.

Answer 1

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 
  todos
WHERE 
  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
  END

This returns the records in the following order:

1
3
2
4

As 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.