Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
ORDER BY clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Databases are allowed to return data in any arbitrary order, unless you explicitly declare that order in the ORDER BY clause. In jOOQ, this is straight-forward:
SELECT AUTHOR_ID, TITLE FROM BOOK ORDER BY AUTHOR_ID ASC, TITLE DESC
create.select(BOOK.AUTHOR_ID, BOOK.TITLE)
.from(BOOK)
.orderBy(BOOK.AUTHOR_ID.asc(), BOOK.TITLE.desc())
.fetch();
Any jOOQ column expression (or field) can be transformed into an org.jooq.SortField by calling the asc() and desc() methods.
Ordering by field index
The SQL standard allows for specifying integer literals (literals, not bind values!) to reference column indexes from the projection (SELECT clause). This may be useful if you do not want to repeat a lengthy expression, by which you want to order - although most databases also allow for referencing aliased column references in the ORDER BY clause. An example of this is given here:
SELECT AUTHOR_ID, TITLE FROM BOOK ORDER BY 1 ASC, 2 DESC
create.select(BOOK.AUTHOR_ID, BOOK.TITLE)
.from(BOOK)
.orderBy(one().asc(), inline(2).desc())
.fetch();
Note, how one() is used as a convenience short-cut for inline(1)
Ordering and NULLS
A few databases support the SQL standard "null ordering" clause in sort specification lists, to define whether NULL values should come first or last in an ordered result.
SELECT
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME
FROM AUTHOR
ORDER BY LAST_NAME ASC,
FIRST_NAME ASC NULLS LAST
create.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME)
.from(AUTHOR)
.orderBy(AUTHOR.LAST_NAME.asc(),
AUTHOR.FIRST_NAME.asc().nullsLast())
.fetch();
If your database doesn't support this syntax, jOOQ emulates it using a CASE expression as follows
SELECT
AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
FROM AUTHOR
ORDER BY LAST_NAME ASC,
CASE WHEN FIRST_NAME IS NULL
THEN 1 ELSE 0 END ASC,
FIRST_NAME ASC
Ordering using CASE expressions
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(choose(BOOK.TITLE)
.when("1984", 0)
.when("Animal Farm", 1)
.otherwise(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(new HashMap<String, Integer>() {{
put("1984", 1);
put("Animal Farm", 13);
put("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();
jOOQ's understanding of SELECT .. ORDER BY
The SQL standard defines that a "query expression" can be ordered, and that query expressions can contain UNION, INTERSECT and EXCEPT clauses, whose subqueries cannot be ordered. While this is defined as such in the SQL standard, many databases allowing for the LIMIT clause in one way or another, do not adhere to this part of the SQL standard. Hence, jOOQ allows for ordering all SELECT statements, regardless whether they are constructed as a part of a UNION or not. Corner-cases are handled internally by jOOQ, by introducing synthetic subselects to adhere to the correct syntax, where this is needed.
Oracle's ORDER SIBLINGS BY clause
jOOQ also supports Oracle's SIBLINGS keyword to be used with ORDER BY clauses for hierarchical queries using CONNECT BY
Feedback
Do you have any feedback about this page? We'd love to hear it!