MySQL: Dynamic ORDER BY clause

When developing some discography software for a website, I came across the need to sort a list of releases differently depending on their release date. I wanted to sort future releases in ascending order (oldest to newest) and sort past releases in descending order (newest to oldest). This isn’t an easy task, but after many hours of trial and error, I finally figured it out with some MySQL trickery.

Given the following MySQL Database table named releases:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title       | varchar(256)     | NO   |     | NULL    |                |
| releasedate | date             | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

I accomplished the dynamic ORDER BY clause by using the following query:

SELECT id, 
       title, 
       releasedate, 
       UNIX_TIMESTAMP(releasedate) AS releasedate_unix, 
       CASE WHEN releasedate > NOW() THEN 0 ELSE 1 END AS futureorpast
FROM releases
ORDER BY futureorpast ASC, 
         CASE WHEN releasedate_unix > UNIX_TIMESTAMP(NOW()) THEN 
            releasedate_unix 
         ELSE 
            (releasedate_unix * -1) 
         END

Now for an explanation. First, we need to “seperate” the results into two sets: future releases and past releases. This is accomplished by calculating the futureorpast column upon which we can sort, so future releases are assigned a value of 0 while past releases are assigned a value of 1. These are then sorted with future releases coming first, because obviously 0 comes before 1.

Next comes the tricky part: we want to sort future releases by ASC but past release by DESC. This is unable to be accomplished using dynamic ASC/DESC in a CASE clause because you can only calculate values using CASE, not clauses. The trick then is to convert the date column into a unix timestamp so that we have an integer, and then take the inverse of that integer on the values we want to sort in reverse.

Using this technique I was able to sort a subset of results in one direction and another subset of the same results in a different direction. I’m not sure if this is the best way to accomplish this, so if anyone has other suggestions on how to accomplish this, I’d love to hear them!