MySQL ORDER BY, use column mod_time, but if mod_time is 0, use column create_time

Question

Hi

I want to order results based on two columns, mod_time and create_time.

I want the most recent to be present. At the moment I have

ORDER BY pr.mod_time DESC, pr.create_time DESC

This breaks if the item has not modified, in which case mod_time is 0 and only the create_time is set. This effectively puts anything with a mod_time of 0 to the end of the ordering even if the create_time is greater than any other mod_time.

I hope that makes sense.

Is there a way I can use ORDER BY to do this?

Thanks, Jake

Answer 1

I'm not sure if this is what you mean, but I'll offer it in case:

Just switch your ORDER BY around:

ORDER BY pr.create_time DESC, pr.mod_time DESC

This will cause it to sort by create_time first.

A side note: You could set mod_time at create time, such that a created item was 'modified' (created) at the same time as create_time. This probably depends on what else is going on in your system though.

Answer 2

You could use this:

ORDER BY CASE WHEN pr.mod_time = 0 THEN pr.create_time
              ELSE pr.mod_time
         END DESC, pr.create_time DESC

Or perhaps this simpler version is want you want, assuming an item will never be modified before it is created:

ORDER BY GREATEST(pr.mod_time, pr.create_time) DESC, pr.create_time DESC

Note that these queries won't be able to use an index, if any.

Answer 3

Add IFNULL(pr.create_time, pr.mod_time) as one of the selected columns.

Specify the position of the IFNULL in the ORDER BY.