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
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.
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.
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.
Table of contents
- 3.3.4.11.1.
- Ordering by field index
- 3.3.4.11.2.
- Ordering and NULLS
- 3.3.4.11.3.
- Ordering using CASE expressions
- 3.3.4.11.4.
- Oracle's ORDER SIBLINGS BY clause
| previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!