Home » Software Development » How to Implement Sort Indirection in SQL

About Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.

How to Implement Sort Indirection in SQL

I’ve recently stumbled upon this interesting Stack Overflow question, where the user essentially wanted to ensure that resulting records are delivered in a well-defined order.

They wrote


FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',

They got


They wanted


Very often, according to your business rules, sorting orders are not “natural”, as in numeric sorting or in alpha-numeric sorting. Some business rule probably specified that GE526OTACCD3ANPH-149839 needs to appear last in a list. Or the user might have re-arranged product names in their screen with drag and drop, producing new sort order.

We could discuss, of course, if such sorting should be performed in the UI layer or not, but let’s assume that the business case or the performance requirements or the general architecture needed for this sorting to be done in the database. How to do it? Through…

Sort Indirection

In fact, you don’t want to sort by the product name, but by a pre-defined enumeration of such names. In other words, you want a function like this:

CE367FAACDHCANPH-151556 -> 1
CE367FAACEX9ANPH-153877 -> 2
NI564FAACJSFANPH-162605 -> 3
GE526OTACCD3ANPH-149839 -> 4

With plain SQL, there are many ways to do the above. Here are two of them (also seen in my Stack Overflow answer):

By using a CASE expression

You can tell the database the explicit sort indirection easily, using a CASE expression in your ORDER BY clause:

FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
  CASE WHEN name = 'CE367FAACDHCANPH-151556' THEN 1
       WHEN name = 'CE367FAACEX9ANPH-153877' THEN 2
       WHEN name = 'NI564FAACJSFANPH-162605' THEN 3
       WHEN name = 'GE526OTACCD3ANPH-149839' THEN 4

Note that I’ve used the CASE WHEN predicate THEN value END syntax, because this is implemented in all SQL dialects. Alternatively (if you’re not using Apache Derby), you could also save some characters when typing, writing:

            WHEN 'CE367FAACEX9ANPH-153877' THEN 2
            WHEN 'NI564FAACJSFANPH-162605' THEN 3
            WHEN 'GE526OTACCD3ANPH-149839' THEN 4

Of course, this requires repeating the same values in the predicate and in the sort indirection. This is why, in some cases, you might be more lucky …


In the following example, the predicate and the sort indirection are taken care of in a simple derived table that is INNER JOIN‘ed to the original query:

SELECT product.name
FROM product
  VALUES('CE367FAACDHCANPH-151556', 1),
        ('CE367FAACEX9ANPH-153877', 2),
        ('NI564FAACJSFANPH-162605', 3),
        ('GE526OTACCD3ANPH-149839', 4)
) AS sort (name, sort)
ON product.name = sort.name
ORDER BY sort.sort

The above example is using PostgreSQL syntax, but you might be able to implement the same in a different way in your database.

Using jOOQ’s sort indirection API

Sort indirection is a bit tedious to write out, which is why jOOQ has a special syntax for this kind of use-case, which is also documented in the manual. Any of the following statements perform the same as the above query:

// jOOQ generates 1, 2, 3, 4 as values in the
// generated CASE expression

// You can choose your own indirection values to
// be generated in the CASE expression
      new HashMap<String, Integer>() {{
        put("CE367FAACDHCANPH-151556", 2);
        put("CE367FAACEX9ANPH-153877", 3);
        put("NI564FAACJSFANPH-162605", 5);
        put("GE526OTACCD3ANPH-149839", 8);


Sort indirection is a nice trick to have up your sleeves every now and then. Never forget that you can put almost arbitrary column expressions in your SQL statement’s ORDER BY clause. Use them!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!


1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design


and many more ....




  1. Stephen McConnell

    This is nice if you already know the all values in the data set. But can you show an example where you DON’T know the values, but you have an odd sort order?

  2. Stephen McConnell


    In the instance of the set of all SKUs in an inventory. The prefix number in the SKU determines the product type (in your above example like CE367FAACDHCANPH representing Orchids and CE367FAACDHTRAPED representing Roses and NI564FAACJSFANPH representing Tulip Bulbs…. etc and the numerical part representing the color and packaging etc). If one wanted to sort thousands of SKUs that represent an inventory how would one do this?

    I’m not saying it couldn’t be done…. And I really like the concept. (I used to work with a boss that thought EVERY thing should be done in SQL). Just wanted to see a possible example.

    The article was VERY interesting and well written.

    • Thanks for the feedback, I really appreciate it. You’ll find lots of other SQL articles on our blog, here: http://blog.jooq.org/category/sql

      Well given your example, I suspect that if this is a use-case, you’ll probably either:

      – Persist that sorting order in another table, i.e. instead of using that VALUES() constructor, you’d actually store the sorting order in a table and join that when needed. This external table can then be partitioned, e.g. by vendor, user, etc.
      – Keep that sorting in application memory, if it is more volatile.

      I like your previous boss. You should send him our contact info :-)

Leave a Reply

Your email address will not be published. Required fields are marked *


Want to take your Java Skills to the next level?
Grab our programming books for FREE!
  • Save time by leveraging our field-tested solutions to common problems.
  • The books cover a wide range of topics, from JPA and JUnit, to JMeter and Android.
  • Each book comes as a standalone guide (with source code provided), so that you use it as reference.
Last Step ...

Where should we send the free eBooks?

Good Work!
To download the books, please verify your email address by following the instructions found on the email we just sent you.