8

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.

Markus
  • 2,526
  • 4
  • 28
  • 35

5 Answers5

22

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.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • I really like the simplicity of this syntax, but could someone explain why it works? – dennisdeems Mar 16 '15 at 14:51
  • 4
    @dennisdeems: this works because `priority=0` is boolean value, and FALSE < TRUE. Since it is first sort criteria, it will push all zeros to the end, but other nonzero values will sort by second sorting criteria normally. – mvp Mar 17 '15 at 07:16
9

Try:

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

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
Sebastian
  • 173
  • 10
1

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.

Kevin Holditch
  • 5,165
  • 3
  • 19
  • 35
0

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

Edgard Leal
  • 2,592
  • 26
  • 30