DZone
Java Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Java Zone > How to Implement Sort Indirection in SQL

How to Implement Sort Indirection in SQL

Lukas Eder user avatar by
Lukas Eder
·
May. 08, 14 · Java Zone · Interview
Like (0)
Save
Tweet
3.03K Views

Join the DZone community and get the full member experience.

Join For Free

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

SELECT name
FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
               'CE367FAACEX9ANPH-153877',
               'NI564FAACJSFANPH-162605',
               'GE526OTACCD3ANPH-149839')

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:

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:

SELECT name
FROM product
WHERE name IN ('CE367FAACDHCANPH-151556',
               'CE367FAACEX9ANPH-153877',
               'NI564FAACJSFANPH-162605',
               'GE526OTACCD3ANPH-149839')
ORDER BY
  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
  END

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:

ORDER BY
  CASE name WHEN 'CE367FAACDHCANPH-151556' THEN 1
            WHEN 'CE367FAACEX9ANPH-153877' THEN 2
            WHEN 'NI564FAACJSFANPH-162605' THEN 3
            WHEN 'GE526OTACCD3ANPH-149839' THEN 4
  END

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 …

By using INNER JOIN

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
JOIN (
  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
DSL.using(configuration)
   .select(PRODUCT.NAME)
   .from(PRODUCT)
   .where(NAME.in(
      "CE367FAACDHCANPH-151556",
      "CE367FAACEX9ANPH-153877",
      "NI564FAACJSFANPH-162605",
      "GE526OTACCD3ANPH-149839"
   ))
   .orderBy(PRODUCT.NAME.sortAsc(
      "CE367FAACDHCANPH-151556",
      "CE367FAACEX9ANPH-153877",
      "NI564FAACJSFANPH-162605",
      "GE526OTACCD3ANPH-149839"
   ))
   .fetch();
 
// You can choose your own indirection values to
// be generated in the CASE expression
   .orderBy(PRODUCT.NAME.sort(
      new HashMap<String, Integer>() {{
        put("CE367FAACDHCANPH-151556", 2);
        put("CE367FAACEX9ANPH-153877", 3);
        put("NI564FAACJSFANPH-162605", 5);
        put("GE526OTACCD3ANPH-149839", 8);
      }}
   ))

Conclusion

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!


Sort (Unix) sql Database

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Synchronization Methods for Many-To-Many Associations
  • Choosing Between GraphQL Vs REST
  • OPC-UA, MQTT, and Apache Kafka: The Trinity of Data Streaming in IoT
  • Product Owner Anti-Patterns

Comments

Java Partner Resources

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo