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

Question

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?

Jason McCreary · Accepted Answer · 2011-12-14 19:50:43Z

40

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.

edited Dec 14, 2011 at 19:50

answered Dec 14, 2011 at 19:45

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

suntrop Over a year ago

Cool :-) Works fine! I have to change the empty ones to NULL instead of 0000-00-00 but then it is exactly what I need.

Jason McCreary Over a year ago

Good. Yes, NULL is a much better representation for row without a date than 0000-00-00.

Andreas Wederbrand · Accepted Answer · 2011-12-14 19:45:59Z

1

Yes

SELECT * 
  FROM table 
  ORDER BY  CASE your_date 
              WHEN '' THEN 'b' 
              ELSE 'a' 
            END,
            date ASC

answered Dec 14, 2011 at 19:45

Andreas Wederbrand

40.5k12 gold badges71 silver badges82 bronze badges

Comments

Randy · Accepted Answer · 2011-12-14 19:46:03Z

0

possibly add a NVL( thedate, to_date('2099-12-31','yyyy-mm-dd')) in the order by clause

answered Dec 14, 2011 at 19:46

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Comments

Pablo Santa Cruz · Accepted Answer · 2011-12-14 19:46:50Z

0

You can use this:

select * from my_table
order by if(isnull(my_field),1,0),my_field;

answered Dec 14, 2011 at 19:46

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

1 Comment

Jason McCreary Over a year ago

Why the use of isnull() and if()

Brian Hoover · Accepted Answer · 2011-12-14 19:47:35Z

-1

Well, as a pure MySQL answer, I would probably do it like this.

  select todo.title, todo.due_date
    from todo
    order by ifnull(todo.due_date, '9999-12-31')

answered Dec 14, 2011 at 19:47

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related