Sql: Sort By Priority, But Put 0 Last

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. Ho

Solution 1:

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

ORDERBY 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.

Solution 2:

A very simple solution could be to use a composite value/ "prefix" for sorting like this:

SELECT ...
FROM ...
ORDERByCASEWHEN priority = 0THEN9999ELSE0END + priority, secondSortCriteriaCol

Solution 3:

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

SELECT t.priority
FROM dbo.testtable t
ORDERBY (CASEWHEN t.priority = 0THEN2147483647ELSE t.priority END)

In case it's not clear I've picked 2147483647 because this is the max value of the priority column so it will be last.

Mark's answer is better and defo one to go with.

Solution 4:

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