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

问题:

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?

回答1:

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.

回答2:

Yes

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

回答3:

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

回答4:

You can use this:

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

回答5:

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')