4

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.

Joshua Pinter
  • 45,245
  • 23
  • 243
  • 245

1 Answers1

14

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.
Joshua Pinter
  • 45,245
  • 23
  • 243
  • 245
  • This is interesting, it seems SQLite is reordering the keys("naturalOrder") (maybe for performance?). This work around is looping the array and building a String (adding 1 loop), then forcing the SQL to read a rather long query to prevent reordering each key, this is placing an additional test on each iterating loop the SQL database does. My reasoning is that a "manual" join will be performed outside the query and that is why the order of keys need to be kept. It would be good to test if this solution (1 extra loop + heavy querying) is better than ... – Delark Nov 22 '22 at 20:58
  • ... (normal query "IN" + reult[] => to Map) and then join both using the map's key. Maybe your solution is good for small arrays, but I think the second one (result[] => map) would be better when the number of items becomes "too much". What needs to be measured is (**foreach** String concat loop + SQL query resolution (delayed by additional condition test) + manual JOIN with **fori** loop) ***VS*** (normal IN query + **foreach** Map building loop + **foreach** manual JOIN with hash gets to key matches on Map (this is another loop)) – Delark Nov 22 '22 at 20:59
  • 1
    @Delark Thanks for the comment. This is certainly not the most performant method and I would be keen to adopt a better approach if you end up doing the benchmark and posting your solution. – Joshua Pinter Nov 22 '22 at 21:15
  • 1
    Thinking about it... your way is better no doubt, creating a Map is too much, besides, the Map foreach loops whenever a new node needs to be searched for a specific key: do { ... } while ((e = e.next) != null); The SQL query may be assigning an empty array of predetermined capacity and using the "ORDER BY CASE " to assign to the empty indices, which may be decreasing near to nothing on performance time. The manual JOIN made with a fori loop is absolutely better than a foreach that looks up for a key match in a map which in return the Map does a secondary loop just to search for the key. – Delark Nov 22 '22 at 22:47