Mysql - ORDER BY - Can to use condition?

I sort a list by date_modified desc

This problem is when a new record input, date_modified is null date 0000-00-00 00:00:00

So this record will be in bottom, not the top of list.

How to sor开发者_如何学JAVAt this ? :(

Note: i don't insert to date_modified with now date , because i have a field date_create

ORDER BY COALESCE(date_modified, date_created) DESC;

But, performance will be better if you set date_modified to CURRENT_TIMESTAMP when doing an INSERT, as was already suggested.

I feel like you need to fix the problem of null date on insertion. Why don't you set the date_modified = now at the time of insertion?

You can do 2 things either sort by the creation date first, then last_modified

SELECT * FROM table ORDER BY created DESC,last_modified DESC

OR you can add the default modified time to the current time

INSERT INTO table (....,last_modified)VALUES (....,NOW());

Now you can make the sorting easily