SQLite and custom order by

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

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

I don't have an "order by" field

Why not?

If it is because the sort order is application sepcific then do the sort in the application rather than in the database.

If it is because the sort order is specific to just this one query then encode it in the query HOWEVER I strongly suggest you make this transparent to the calling application e.g. (Standard SQL syntax):

SELECT T2.ID, T2.Category,        T2.query_name_here_sort_order  FROM (        SELECT T1.ID, T1.Category,                CASE ID                  WHEN 4 THEN 1                  WHEN 3 THEN 2                  WHEN 1 THEN 3                  WHEN 5 THEN 4                  WHEN 6 THEN 5               END AS query_name_here_sort_order          FROM CATEGORIES AS T1       ) AS T2 (                ID, Category,                 query_name_here_sort_order               ) ORDER     BY query_name_here_sort_order;

If the sort order is used by multiple queries and/or applications then add it to a column in a table in the database then use that column in your query.