57

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!

4 Answers4

79

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;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • +1. Here's another elegant and compact standard solution (available since [SQL:2003 extension T611](http://en.wikipedia.org/wiki/Order_by), although not all vendors implement this functionality): http://stackoverflow.com/a/12767777/814702. – informatik01 Jan 31 '14 at 14:50
  • 1
    @informatik01, good tip, but this question is tagged `mysql` and MySQL doesn't support that syntax. – Bill Karwin Jan 31 '14 at 23:07
  • @BillKarwin Yeah, sad. Only checked this on PostgreSQL 9.1, where it works just fine. Anyways, let this info be here just for the sake of completeness. P.S. Just noticed that when using `ORDER BY` MySQL and PostgreSQL behave differently: when ordering *ascending* MySQL puts NULL values **first**, while PostgreSQL puts NULL values **last**. Well, vendor specific stuff ... – informatik01 Feb 01 '14 at 12:01
  • To explain how this works - http://stackoverflow.com/questions/26985068/why-does-this-sql-order-null-values-last#26985092 – Chris Halcrow Nov 18 '14 at 01:37
  • Yes, NULLS FIRST is an example of something that makes this simple task simple, but sadly not all DBMSs support it. Another example of a simple task that some DBMSs make really difficult is dropping a table only if it exists. http://www.ehow.com/how_4738206_database-table-only-already-exists.html – Stewart Dec 16 '14 at 12:32
  • @BillKarwin - I am new to sql. Why can't we do `order by desc` for the column which has nulls ? That seems to work for me. – MasterJoe Sep 01 '20 at 19:27
  • 1
    @MasterJoe, you can order by any column you want. But in MySQL, NULL is ordered before any other value. You could use `order by ... desc` as you say, but then all the values would be sorted in reverse order. I assume the question wants them sorted ascending, except for NULLs which should be _after_ all other values. – Bill Karwin Sep 01 '20 at 21:27
29
SELECT * FROM myTable
WHERE ...
ORDER BY ISNULL(myDate), myDate
nickf
  • 537,072
  • 198
  • 649
  • 721
4
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

Perry
  • 11,172
  • 2
  • 27
  • 37
Danimal
  • 7,672
  • 8
  • 47
  • 57
  • "case isnull(due_date, 0) when 0 then 1 else 0" - why bother doing all that? isnull returns 1 or 0 anyway. – nickf Sep 29 '08 at 23:41
  • In MSSQL, ISNULL returns its first argument, or its second argument if its first argument is null. There's a standard SQL function COALESCE which does the same and also accepts more than two arguments. But this is a bit off-topic since the poster is asking about MySQL.... – Stewart Dec 16 '14 at 12:24
3

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.

luke77
  • 2,255
  • 2
  • 18
  • 30