Menu
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.
721
If you sort a column with NULL values in ascending order, the NULLs will come first. Alternatively, if you add a DESC keyword to get a descending order, NULLs will appear last.
If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.
The order statement generates a 'virtual field' that contains NULL or 0 based on the case condition. The ORDER BY is applied based on that virtual field. The zero value in the order by precedes the NULL value. That's all, it's a way to set NULLs of the field1 at the end of the order list.
Note: The SQL Order by clause only provides sorting of the records in the results set. The Order by clause does not affect the ordering of the records in the source table or changing the physical structure of the table. It is just a logical re-structuring of physical data.
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.
181
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
