SQLite and custom order by

Question

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.

Answer 1

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 

Answer 2

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=3 DESC,          ID=1 DESC,          ID=5 DESC,          ID=6 DESC