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!

I do a lot of command line work on my OS X machine, so the history saves me a lot of time running repeat commands and also refreshing my memory on commands that I haven’t run in a while. Unfortunately, the default history length in OS X is 500 commands. That seems like a lot, but when you’re running 50+ commands a day it can push older commands off the list pretty quickly. This is easily solved by setting the HISTFILESIZE in your .bash_profile file.

First, to find out what HISTFILESIZE is currently set at, run the following command from Terminal:

echo $HISTFILESIZE

To change this value, simply add the following line to your .bash_profile file (found in /Users/yourname/):

HISTFILESIZE=2000

This increases your bash history to 2000 items. It will take effect next time you open a Terminal window.