SQL: Sort by priority, but put the last 0

Question

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

However, items without priority (currently priority 0) must be listed by another column after the priorities.

In other words. If I have these priorities:

1 2 0 0 5 0 8 9 

How to sort them as follows:

  1 2 5 8 9 0 0 0 

I think I could use Int.max instead of 0, but 0 is such a good default value that I would try to save.

Answer 1

Try:

 order by case priority when 0 then 2 else 1 end, priority 

Answer 2

I do not think that it can become cleaner than this:

 ORDER BY priority=0, priority 

SQLFiddle Demo

Please note that unlike any other solutions, this one will use the index on priority and will be fast if the number of entries is large.

Answer 3

A very simple solution would be to use a compound value / "prefix" to sort as follows:

 SELECT ... FROM ... ORDER By CASE WHEN priority = 0 THEN 9999 ELSE 0 END + priority, secondSortCriteriaCol 

Answer 4

This will do the trick. You will need to replace testtable with the name of your table.

 SELECT t.priority FROM dbo.testtable t ORDER BY (CASE WHEN t.priority = 0 THEN 2147483647 ELSE t.priority END) 

If this is not clear, I chose 2147483647, because this is the maximum value of the priority column, so it will be the last.

It’s better to mark the answer, and defo to go.

Answer 5

order by case(priority) when 0 then 10 else priority end