This site uses third-party cookies, learn more or accept
dark light

SQL Conditional Order By

Written by Max Pelic on

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

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

Share this article:

Previous Article: Using the Canvas element to save an image

Next Article: Moving away from AMP