CODEWITHSUNDEEP
sqlmysql
Reid Wilson
Reid Wilson

Reputation:

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

I have a bunch of tasks in a MySQL database, and one of the fields is "deadline date". Not every task has to have to a deadline date.

I'd like to use SQL to sort the tasks by deadline date, but put the ones without a deadline date in the back of the result set. As it is now, the null dates show up first, then the rest are sorted by deadline date earliest to latest.

Any ideas on how to do this with SQL alone? (I can do it with PHP if needed, but an SQL-only solution would be great.)

Thanks!

Upvotes: 60

Views: 25055

Answers (4)

luke77
luke77

Reputation: 3703

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.

Upvotes: 5

Danimal
Danimal

Reputation: 7710

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

Upvotes: 4

Bill Karwin
Bill Karwin

Reputation: 562368

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;

Upvotes: 81

nickf
nickf

Reputation: 546055

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

Upvotes: 29

Related Questions