Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PHP, MYSQL: Order by date but empty dates last not first

I fetch an array with todo titles and due dates from MySQL. I want to order it by date and have the oldest on top. But there are some todos without a date. These todos I don't want to show at first positions but rather at the bottom of my list. Unfortunately MySQL put the empty ones first.

Is there any way I can do it in one query (can't use MySQLi, using CI's ActiveRecord). I could run a second query for all todos without dates and put them at the bottom. But I'd like to make it in one query – if possible?

like image 695
suntrop Avatar asked Dec 14 '11 19:12

suntrop


1 Answers

You can do it in MySQL with the ORDER BY clause. Sort by NULL first, then the date.

SELECT * FROM your_table ORDER BY (date_column IS NULL), date_column ASC

Note: This assumes rows without a date are NULL.

like image 81
Jason McCreary Avatar answered Sep 18 '22 21:09

Jason McCreary