Custom ordering in sqlite

Question

Is there a way to have a custom order by query in sqlite?

For example, I have essentially an enum

_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 have ordering. Say Key > Named > Contributing.

Is there a way to make

SELECT * FROM table ORDER BY Key

return something to the effect of

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

this?

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's (or complicated set of conditions), Adam's solution may result in an extremely large query.

SQLite does allow you to write your own functions (in C++). You could write a function to return values similar to the way Adam does, but because you're using C++, you could work with a much larger set of conditions (or separate table, etc).

Once the function is written, you can refer to it in your SELECT as if it were a built-in function:

SELECT * FROM my_table ORDER BY MyOrder(Key)