|previous : next|
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
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The SQL:2003 standard supports a
WINDOW clause that allows for specifying
WINDOW frames for reuse in SELECT clauses and ORDER BY clauses.
SELECT LAG(first_name, 1) OVER w "prev", first_name, LEAD(first_name, 1) OVER w "next" FROM author WINDOW w AS (ORDER first_name) ORDER BY first_name DESC
WindowDefinition w = name("w").as( orderBy(PEOPLE.FIRST_NAME)); create.select( lag(AUTHOR.FIRST_NAME, 1).over(w).as("prev"), AUTHOR.FIRST_NAME, lead(AUTHOR.FIRST_NAME, 1).over(w).as("next")) .from(AUTHOR) .window(w) .orderBy(AUTHOR.FIRST_NAME.desc()) .fetch();
Note that in order to create such a window definition, we need to first create a name reference using DSL.name().
Even if only PostgreSQL and Sybase SQL Anywhere natively support this great feature, jOOQ can emulate it by expanding any
org.jooq.WindowSpecification types that you pass to the
window() method - if the database supports window functions at all.
Some more information about window functions and the
WINDOW clause can be found on our blog: https://blog.jooq.org/probably-the-coolest-sql-feature-window-functions/
Do you have any feedback about this page? We'd love to hear it!