Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Possible to use SQL to sort by date but put null dates at the back of the results set?

Tags:

sql

mysql

People also ask

How do I insert a NULL in a date column in SQL?

"NULL" can be specified as a value in the Date field to get an empty/blank by using INSERT statement. Example: CREATE table test1 (col1 date); INSERT into test1 values (NULL);

Can a date field be NULL in SQL?

A NULL date is NULL (no value). An empty string, on the other hand, evaluates to 0 , which in SQL Server is implicitly an integer representing the number of days since 1900-01-01 .

How are NULL values handled when data is sorted?

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.


Here's a solution using only standard SQL, not ISNULL(). That function is not standard SQL, and may not work on other brands of RDBMS.

SELECT * FROM myTable
WHERE ...
ORDER BY CASE WHEN myDate IS NULL THEN 1 ELSE 0 END, myDate;

SELECT * FROM myTable
WHERE ...
ORDER BY ISNULL(myDate), myDate

SELECT foo, bar, due_date FROM tablename
ORDER BY CASE ISNULL(due_date, 0)
WHEN 0 THEN 1 ELSE 0 END, due_date

So you have 2 order by clauses. The first puts all non-nulls in front, then sorts by due date after that


The easiest way is using the minus operator with DESC.

SELECT * FROM request ORDER BY -date DESC

In MySQL, NULL values are considered lower in order than any non-NULL value, so sorting in ascending (ASC) order NULLs are listed first, and if descending (DESC) they are listed last.

When a - (minus) sign is added before the column name, NULL become -NULL.

Since -NULL == NULL, adding DESC make all the rows sort by date in ascending order followed by NULLs at last.


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!