SQL how to make null values come last when sorting ascending

user_55377 0
Viewed 318410

I have a SQL table with a datetime field. The field in question can be null. I have a query and I want the results sorted ascendingly by the datetime field, however I want rows where the datetime field is null at the end of the list, not at the beginning.

Is there a simple way to accomplish that?

sql-order-by sorting sql
14 Answers

When your order column is numeric (like a rank) you can multiply it by -1 and then order descending. It will keep the order you're expecing but put NULL last.

select *
from table
order by -rank desc
user_9123250

If you're using MariaDB, they mention the following in the NULL Values documentation.

Ordering

When you order by a field that may contain NULL values, any NULLs are considered to have the lowest value. So ordering in DESC order will see the NULLs appearing last. To force NULLs to be regarded as highest values, one can add another column which has a higher value when the main field is NULL. Example:

SELECT col1 FROM tab ORDER BY ISNULL(col1), col1;

Descending order, with NULLs first:

SELECT col1 FROM tab ORDER BY IF(col1 IS NULL, 0, 1), col1 DESC;

All NULL values are also regarded as equivalent for the purposes of the DISTINCT and GROUP BY clauses.

The above shows two ways to order by NULL values, you can combine these with the ASC and DESC keywords as well. For example the other way to get the NULL values first would be:

SELECT col1 FROM tab ORDER BY ISNULL(col1) DESC, col1;
--                                         ^^^^
user_39825620
Related