Custom order in sqlite

Question

Is there a way to make my own order on demand in sqlite?

For example, I have essentially an enumeration

_id | Name | Key
------------
1 | One | Named
2 | Two | Contributing
3 | Three | Named
4 | Four | Key
5 | Five | Key
6 | Six | Contributing
7 | Seven | Named

And the key columns are in order. Say Key> Named> Contributing.

Is there any way to do

SELECT * FROM table ORDER BY Key

bring something back into action

_id | Name | Key
------------
4 | Four | Key
5 | Five | Key
1 | One | Named
3 | Three | Named
7 | Seven | Named
2 | Two | Contributing
6 | Six | Contributing

Answer 1

  SELECT _id, Name, Key 
    FROM my_table t 
ORDER BY CASE WHEN key = 'Key' THEN 0 
              WHEN key = 'Named' THEN 1 
              WHEN key = 'Contributing' THEN 2 END, id;

Answer 2

If you have a lot of CASE (or a complex set of conditions), Adam's decision can lead to an extremely large query.

SQLite ( ++). , , , , ++, ( ..).

, SELECT, :

SELECT * FROM my_table ORDER BY MyOrder ()

Answer 3

You tried (not tested on my side, but relied on a technique that I previously used):

ORDER BY KEY = "Key" DESC,
         KEY = "Named" DESC,
         KEY = "Contributing" DESC