Sorting Results by Specific Values in SQL with CASE

Recently I rewrote the backend for my service, Any New Books, due to an Amazon API change. Generally speaking, rewriting an entire codebase is a bad idea. However, the code was quite old and the API changes rather drastic, so I took the opportunity to rewrite the whole thing in Rails 6.

This service allows you to subscribe (100% for free) to categories of your choosing and then receive a weekly email for each category with a selection of newly released books.

In the backend, the algorithm uses a few heuristics to determine how to present new books for a given category, so that the best candidates surface to the top to be manually selected by a (human) editor.

Sorting results by specific values with CASE

One of the parameters to consider is, believe it or not, whether a book’s binding is hardcover, paperback, Kindle, or audiobook. So at some point, I needed to sort results by these specific values in SQL.

In the backend I rely on PostgreSQL, so I used the following simple case statement:

SELECT *
FROM   books
ORDER  BY CASE binding
            WHEN 'Hardcover' THEN 1
            WHEN 'Paperback' THEN 2
            WHEN 'Kindle Edition' THEN 3
            WHEN 'Audiobook' THEN 4
            ELSE 5
          END

This will order books placing hardcover books first, then paperbacks, then Kindle Edition ones, then audiobooks. And if a book has a binding/format that was not included in the list (e.g., MP3 CD), it will be placed last.

SQL CASE statements like this are quite handy and will work in the majority of modern relational databases.

Ordering by multiple fields

This is cool, but can we do if we also want independently published books (whose publisher in the database is “Independently Published”) to appear first in the list? (Hey, let’s throw a bone to the indie crowd.)

That has nothing to do with binding used as the condition for the CASE statement; it’s another field altogether. 

Here is what we cannot do:

SELECT *
FROM   books
ORDER  BY publisher,
          CASE binding
            WHEN 'Hardcover' THEN 1
            WHEN 'Paperback' THEN 2
            WHEN 'Kindle Edition' THEN 3
            WHEN 'Audiobook' THEN 4
            ELSE 5
          END

I mean, that will execute. But a book published by “Acme” will appear before independently published books which is not what we want. Likewise, if we use ORDER BY publisher DESC, ... books published by, say, “Wrox” will appear before independently published ones. (Wrox books tend to be great but still, it’s not what we want.)

You might be tempted to comma separate two CASE statements, one to sort by publisher and the existing one to sort by binding. Thankfully we don’t have to do that. There is a much simpler way using a single CASE statement:

SELECT *
FROM   books
ORDER  BY CASE
            WHEN publisher = 'Independently Published' THEN 1
            WHEN binding = 'Hardcover' THEN 2
            WHEN binding = 'Paperback' THEN 3
            WHEN binding = 'Kindle Edition' THEN 4
            WHEN binding = 'Audiobook' THEN 5
            ELSE 6
          END

The LIKE operator in a CASE statement

We can even inject a LIKE operator to pick up books whose publishers contain the string “Independent”:

SELECT *
FROM   books
ORDER  BY CASE
            WHEN publisher LIKE '%Independent%' THEN 1
            WHEN binding = 'Hardcover' THEN 2
            WHEN binding = 'Paperback' THEN 3
            WHEN binding = 'Kindle Edition' THEN 4
            WHEN binding = 'Audiobook' THEN 5
            ELSE 6
          END

Thanks to the ILIKE variant, we can make the LIKE operator case insensitive:

SELECT *
FROM   books
ORDER  BY CASE
            WHEN publisher ILIKE '%independent%' THEN 1
            WHEN binding = 'Hardcover' THEN 2
            WHEN binding = 'Paperback' THEN 3
            WHEN binding = 'Kindle Edition' THEN 4
            WHEN binding = 'Audiobook' THEN 5
            ELSE 6
          END

There you have it!

This is basic stuff, no two ways about it. Lately, though, I’ve been reflecting more on the fact that what’s basic to me might not be so basic to people who are just starting out.

As a result, I want to share more on this blog and hope that in doing so, I will help someone, somewhere. Make sure you subscribe below if you are interested in seeing further content like this.

The Art of PostgreSQL is amazing

While we are on the topic, I cannot stress my recommendation for The Art of PostgreSQL highly enough. It houses fantastic content for those who already have a basic understanding of SQL and wish to take their knowledge of PostgreSQL (and SQL in general) to the next level.

Originally published on Programming Zen.