Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Ordering using CASE expressions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Using CASE expressions in SQL ORDER BY clauses is a common pattern, if you want to introduce some sort indirection / sort mapping into your queries. As with SQL, you can add any type of column expression into your ORDER BY clause.
For instance, if you have two favourite books that you always want to appear on top, you could write:
SELECT *
FROM BOOK
ORDER BY CASE TITLE
WHEN '1984' THEN 0
WHEN 'Animal Farm' THEN 1
ELSE 2 END ASC
create.select()
.from(BOOK)
.orderBy(case_(BOOK.TITLE)
.when("1984", 0)
.when("Animal Farm", 1)
.else_(2).asc())
.fetch();
But writing these things can become quite verbose. jOOQ supports a convenient syntax for specifying sort mappings. The same query can be written in jOOQ as such:
create.select()
.from(BOOK)
.orderBy(BOOK.TITLE.sortAsc("1984", "Animal Farm"))
.fetch();
More complex sort indirections can be provided using a Map:
create.select()
.from(BOOK)
.orderBy(BOOK.TITLE.sort(Map.of(
"1984", 1,
"Animal Farm", 13,
"The jOOQ book", 10
)))
.fetch();
Of course, you can combine this feature with the previously discussed NULLS FIRST / NULLS LAST feature. So, if in fact these two books are the ones you like least, you can put all NULLS FIRST (all the other books):
create.select()
.from(BOOK)
.orderBy(BOOK.TITLE.sortAsc("1984", "Animal Farm").nullsFirst())
.fetch();
Dialect support
This example using jOOQ:
select(BOOK.ID).from(BOOK).orderBy(BOOK.TITLE.sortAsc("1984", "Animal Farm"))
Translates to the following dialect specific expressions:
Access
SELECT BOOK.ID FROM BOOK ORDER BY SWITCH(BOOK.TITLE = '1984', 0, BOOK.TITLE = 'Animal Farm', 1) ASC
ASE, Aurora MySQL, Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
SELECT BOOK.ID FROM BOOK ORDER BY CASE BOOK.TITLE WHEN '1984' THEN 0 WHEN 'Animal Farm' THEN 1 END ASC
Derby
SELECT BOOK.ID FROM BOOK ORDER BY CASE WHEN BOOK.TITLE = '1984' THEN 0 WHEN BOOK.TITLE = 'Animal Farm' THEN 1 END ASC
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!