CASE Expressions, for complex Order Bys in SQL
So here’s the problem I was having:
I wanted to order a set of data by Date End, with ‘date_end’ a datetime column in our database.
However, the data could also be 00-00-0000 00:00:00 – which represents ‘Present’, where there is no end date.
Heres some data as an example:
| id | date_start | date_end |
|---|---|---|
| A | 2010-02-01 00:00:00 | 2011-02-01 00:00:00 |
| B | 2011-02-01 00:00:00 | 2012-02-01 00:00:00 |
| C | 2013-02-01 00:00:00 | 0000-00-00 00:00:00 |
| D | 2009-02-01 00:00:00 | 2010-02-01 00:00:00 |
| E | 2008-02-01 00:00:00 | 2009-02-01 00:00:00 |
A standard SQL query with “ORDER BY date_end DESC” would return:
| id | date_start | date_end |
|---|---|---|
| B | 2011-02-01 00:00:00 | 2012-02-01 00:00:00 |
| A | 2010-02-01 00:00:00 | 2011-02-01 00:00:00 |
| D | 2009-02-01 00:00:00 | 2010-02-01 00:00:00 |
| E | 2008-02-01 00:00:00 | 2009-02-01 00:00:00 |
| C | 2013-02-01 00:00:00 | 0000-00-00 00:00:00 |
To get the Present one at the top, we can use the Case expression.
“CASE date_end WHEN 00-00-00 00:00:00 THEN 1 ELSE 2 END”
So CASE date_end is the column we have selected. WHEN selects the value we are looking for, and then THEN and ELSE assign values to the rows based on whether they match the value we are looking for. So we can visualise the result as something like this:
| id | date_start | date_end | case |
|---|---|---|---|
| A | 2010-02-01 00:00:00 | 2011-02-01 00:00:00 | 2 |
| B | 2011-02-01 00:00:00 | 2012-02-01 00:00:00 | 2 |
| C | 2013-02-01 00:00:00 | 0000-00-00 00:00:00 | 1 |
| D | 2009-02-01 00:00:00 | 2010-02-01 00:00:00 | 2 |
| E | 2008-02-01 00:00:00 | 2009-02-01 00:00:00 | 2 |
So now we can order by our case statement, and then by date_end as a secondary clause.
“ORDER BY CASE date_end WHEN 00-00-00 00:00:00 THEN 1 ELSE 2 END, date_end DESC”
Which gives us our dates ordered by date with most recent (present) first:
| id | date_start | date_end |
|---|---|---|
| C | 2013-02-01 00:00:00 | 0000-00-00 00:00:00 |
| B | 2011-02-01 00:00:00 | 2012-02-01 00:00:00 |
| A | 2010-02-01 00:00:00 | 2011-02-01 00:00:00 |
| D | 2009-02-01 00:00:00 | 2010-02-01 00:00:00 |
| E | 2008-02-01 00:00:00 | 2009-02-01 00:00:00 |
Finally, as we are using Laravel as our PHP Framework, we can use Laravel’s Fluent Query Builderalongside a Raw Query.
DB::table(‘history’)
->order_by(DB::raw(‘case date_end when 0 then 1 else 2 end’))
->order_by(‘date_end’, ‘desc’);