Sorting Two MySQL Table Columns as One

How do you sort database entries chronologically when there are two different date fields? One shows when the entry was updated. The other indicates when it was created. If the developer didn't have the hindsight to set both fields to the same date when an entry is created, how do you work with the fields for sorting?

The first thing that may come to mind is adding both fields to the query'sĀ ORDER BY clause.

...ORDER BY updated, created

The problem is the updated field will take precedence. The created field isn't even considered unless there are multiple entries that were updated on the same date. Instead, we need to merge the date fields somehow. Well, that's where the IFNULL() function could be handy.

SELECT IFNULL(updated, created) AS updated FROM tableName ORDER BY updated

The IFNULL() function checks if the updated field contains a NULL value. If it does, the value of the created field is returned. Otherwise, updated is used. The returned value is stored in the new updated field used by the ORDER BY clause.

Example

If the database table looks like the following:

id created updated
1 2012-07-11 NULL
2 2011-01-01 2012-06-10
3 2012-01-01 NULL
4 2012-01-01 2012-05-01
5 2012-02-01 NULL

The sorted query results would be

  • 2012-01-01
  • 2012-02-01
  • 2012-05-01
  • 2012-06-10
  • 2012-07-11

Conclusion

As mentioned earlier, it would have been easier if both fields (created and updated) were set the same date when an entry is first created. However, no code is perfect. Issues such as this may come up and it's beneficial having alternate solutions when there isn't time or resources to make it right.

2 Comments

  • #1 Reply via Twitter on 07.16.12 at 10:16 pm

    Comment from @MichaelPierre via Twitter:

    @pnichman thanks for the 2 helpful tips: dev's foresight and IFNULL()

  • #2 Jon on 10.31.13 at 4:54 am

    Very useful tip!
    Thanks!!

Leave a Comment