sqlite - SQL: Sort by priority, but put 0 last

Question

I have a (int) column called "priority". When I select my items I want the highest priority (lowest number) to be first, and the lowest priority (highest number) to be the last.

However, the items without a priority (currently priority 0) should be listed by some other column after the ones with a priority.

In other words. If I have these priorities:

 1 2 0 0 5 0 8 9

How do I sort them like this:

 1 2 5 8 9 0 0 0 

I guess I could use Int.max instead of 0, but 0 makes up such a nice default value which I would try to keep.

Answer 1

I don't think it can get cleaner than this:

ORDER BY priority=0, priority

SQLFiddle Demo

Note that unlike any other solutions, this one will take advantage of index on priority and will be fast if number of records is large.