SQL Conditional Order By

I recently ran into a problem where I needed to order MySQL results differently depending on a value in the row. The table contained rows with dates in them, and I wanted to show upcoming dates first in ascending order (closest dates first), then show the rest of the results sorted in descending order. With a little trial and error I was able to make it work, here’s what I did:

First, I separated them into two groups using a simple order by statement: ORDER BY date >= NOW() DESC. That way, the future dates show up first, and the rest show up below/after those.

Next, I added an if statement to the ORDER BY clause. I originally wanted to do something like IF(date >= NOW(), date ASC, date DESC), but that didn’t work, I couldn’t include ASC and DESC in the if statement. So I ended up doing it a little differently: IF(date >= NOW(), UNIX_TIMESTAMP(date), - UNIX_TIMESTAMP(date)) ASC. That way the future dates were sorted by date ASC and the past dates were sorted by the negative value of the date in ascending order, which ends up being the date in descending order.

So the full order by statement ended up being

ORDER BY date >= NOW() DESC, IF(date >= NOW(), UNIX_TIMESTAMP(date), - UNIX_TIMESTAMP(date)) ASC;