CODEWITHSUNDEEP
sqlsqlitesql-order-byandroid-sqlite
Markus
Markus

Reputation: 2564

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.

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.

Upvotes: 9

Views: 12169

Answers (5)

mvp
mvp

Reputation: 116247

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.

Upvotes: 24

Edgard Leal
Edgard Leal

Reputation: 2720

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

Upvotes: 0

Kevin Holditch
Kevin Holditch

Reputation: 5303

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

SELECT t.priority
FROM dbo.testtable t
ORDER BY (CASE WHEN t.priority = 0 THEN 2147483647 ELSE 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.

Upvotes: 1

Sebastian
Sebastian

Reputation: 173

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

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

Upvotes: 2

user359040
user359040

Reputation:

Try:

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

Upvotes: 9

Related Questions