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.5.3.11.1.
- Ordering by field index
- 3.5.3.11.2.
- Ordering and NULLS
- 3.5.3.11.3.
- Ordering using CASE expressions
- 3.5.3.11.4.
- Oracle's ORDER SIBLINGS BY clause
| previous : next |
References to this page
- Implicit join type
- CONNECT BY clause
- WINDOW clause
- Differences to standard SQL
- Lexical and logical SELECT clause order
- Scalar functions
- Grouping
- The CASE expression
- Optional column expressions
- Unnecessary EXISTS subquery clauses
- Unnecessary ORDER BY expressions
- Unnecessary scalar subquery
- Zero-based vs one-based APIs
- SQL: Rely on implicit ordering
Feedback
Do you have any feedback about this page? We'd love to hear it!