SQLite and custom order by

sql, sql-order-by, sqlite

Solution

ORDER BY 
  CASE ID
    WHEN 4 THEN 0
    WHEN 3 THEN 1
    WHEN 1 THEN 2
    WHEN 5 THEN 3
    WHEN 6 THEN 4
  END

Problem

I have a table with categories: ``` ID Category "1","Baking" "3","Family" "4","Entertaining" "5","Children" "6","Desserts" ``` Now I would like to order the result of the select statement to ``` ID Category "4","Entertaining" "3","Family" "1","Baking" "5","Children" "6","Desserts" ``` for example. In MySQL, you'd do it like this: ``` SELECT * FROM CATEGORIES ORDER BY FIELD (ID, 4,3,1,5,6); ``` How would you do it in SQLite though? I don't have an "order by" field.

Original source