In SQL, the ORDER BY clause is commonly used to order the results of a query. It allows you to select one or more columns to order the results, and in most cases, it’s probably all you need.
But what if you need to make an exception?
What if you want the results to be ordered alphabetically, except for one row? Or several rows?
Or perhaps you simply want to put any NULL values to the end, while ordering the non-NULL results.
Either way, there’s a neat trick you can use that will enable you to do this. And the good part is, it’s simple.
You can cater for all of the above scenarios by adding a CASE expression to your ORDER BY clause.
Example 1 – Move “Other” to the Bottom
Suppose we run the following query against a table containing music genres.
SELECT Genre
FROM MusicGenres
ORDER BY Genre ASC;Result:
+---------+ | Genre | |---------| | Blues | | Country | | Hip Hop | | Jazz | | Metal | | Other | | Pop | | Rap | | Rock | +---------+
In this case, we order the results by the Genre column, in ascending order.
This is fine except for one thing. The genre called Other. Wouldn’t it be nice if we could move Other to the bottom?
We can achieve this with the CASE expression. Therefore, we can take the above query, and modify its ORDER BY clause as follows.
SELECT Genre
FROM MusicGenres
ORDER BY
CASE Genre
WHEN 'Other' THEN 1
ELSE 0
END
ASC, Genre ASC;Result:
+---------+ | Genre | |---------| | Blues | | Country | | Hip Hop | | Jazz | | Metal | | Pop | | Rap | | Rock | | Other | +---------+
Example 2 – Move NULLs to the Bottom
If your table happens to contain any of them pesky NULL values, you’ll find that they’ll insist on staying at the top when you order in ascending order.
Once again, CASE expression to the rescue!
Let’s imagine that the above table contains a couple NULL values. And when we run our query, it looks more like this:
SELECT Genre
FROM MusicGenres
ORDER BY
CASE Genre
WHEN 'Other' THEN 1
ELSE 0
END
ASC, Genre ASC;Result:
+---------+ | Genre | |---------| | NULL | | NULL | | Blues | | Hip Hop | | Jazz | | Metal | | Pop | | Rock | | Other | +---------+
So now we want to move the NULL values to the bottom – even lower than Other.
We can do that with the following query.
SELECT Genre
FROM MusicGenres
ORDER BY
CASE
WHEN Genre IS NULL THEN 2
WHEN Genre = 'Other' THEN 1
ELSE 0
END
ASC, Genre ASC;Result:
+---------+ | Genre | |---------| | Blues | | Hip Hop | | Jazz | | Metal | | Pop | | Rock | | Other | | NULL | | NULL | +---------+
In this example, we used a different CASE format. In this example we used a searched CASE expression, as opposed to the previous example which, used a simple CASE expression.
The searched CASE expression evaluates a set of Boolean expressions to determine the result.
The simple CASE expression, on the other hand, compares an expression to a set of simple expressions to determine the result.
The simple CASE expression has an input expression next to the CASE keyword, whereas, the searched CASE expression doesn’t.
Example 3 – Fix Certain Rows to the Top
Now imagine we want to have one or more rows that are always at the top of the results, regardless of where they fit within the ordering of the wider results.
For example:
SELECT * FROM vAlbums
ORDER BY ArtistName ASC, AlbumName ASC;Result:
+------------------------+--------------------------+---------+ | ArtistName | AlbumName | Genre | |------------------------+--------------------------+---------| | AC/DC | Powerage | Rock | | Allan Holdsworth | All Night Wrong | Jazz | | Allan Holdsworth | The Sixteen Men of Tain | Jazz | | Buddy Rich | Big Swing Face | Jazz | | Devin Townsend | Casualties of Cool | Rock | | Devin Townsend | Epicloud | Rock | | Devin Townsend | Ziltoid the Omniscient | Rock | | Iron Maiden | Killers | Rock | | Iron Maiden | No Prayer for the Dying | Rock | | Iron Maiden | Piece of Mind | Rock | | Iron Maiden | Powerslave | Rock | | Iron Maiden | Somewhere in Time | Rock | | Jim Reeves | Singing Down the Lane | Country | | Michael Learns to Rock | Blue Night | Pop | | Michael Learns to Rock | Eternity | Pop | | Michael Learns to Rock | Scandinavia | Pop | | The Script | No Sound Without Silence | Pop | | Tom Jones | Along Came Jones | Pop | | Tom Jones | Long Lost Suitcase | Pop | | Tom Jones | Praise and Blame | Pop | +------------------------+--------------------------+---------+
These results are ordered by ArtistName, and then by AlbumName.
But the record company has decided that they want to do a special promotion for Tom Jones. And so they want Tom Jones to appear at the top of the results, but then all the remaining results are to be ordered as is – alphabetically by the artist name, then by the album name.
In this case, we can do the following:
SELECT * FROM vAlbums
ORDER BY
CASE ArtistName
WHEN 'Tom Jones' THEN 0
ELSE 1
END,
ArtistName ASC, AlbumName ASC;Result:
+------------------------+--------------------------+---------+ | ArtistName | AlbumName | Genre | |------------------------+--------------------------+---------| | Tom Jones | Along Came Jones | Pop | | Tom Jones | Long Lost Suitcase | Pop | | Tom Jones | Praise and Blame | Pop | | AC/DC | Powerage | Rock | | Allan Holdsworth | All Night Wrong | Jazz | | Allan Holdsworth | The Sixteen Men of Tain | Jazz | | Buddy Rich | Big Swing Face | Jazz | | Devin Townsend | Casualties of Cool | Rock | | Devin Townsend | Epicloud | Rock | | Devin Townsend | Ziltoid the Omniscient | Rock | | Iron Maiden | Killers | Rock | | Iron Maiden | No Prayer for the Dying | Rock | | Iron Maiden | Piece of Mind | Rock | | Iron Maiden | Powerslave | Rock | | Iron Maiden | Somewhere in Time | Rock | | Jim Reeves | Singing Down the Lane | Country | | Michael Learns to Rock | Blue Night | Pop | | Michael Learns to Rock | Eternity | Pop | | Michael Learns to Rock | Scandinavia | Pop | | The Script | No Sound Without Silence | Pop | +------------------------+--------------------------+---------+