The jOOQ User Manual SQL building SQL Statements (DML) The SELECT statement WINDOW clause

previous : next

WINDOW clause

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(AUTHOR.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.WindowDefinition and 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/

Dialect support

This example using jOOQ:

select(rowNumber().over("w")).from(AUTHOR).window(name("w").as(orderBy(AUTHOR.ID)))

Translates to the following dialect specific expressions:

Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Databricks, DuckDB, Exasol, Firebird, H2, MySQL, Oracle, Postgres, SQLServer, SQLite, Sybase, Trino, YugabyteDB

SELECT row_number() OVER w
FROM AUTHOR
WINDOW w AS (ORDER BY AUTHOR.ID)

DB2, Hana, Informix, MariaDB, MemSQL, SQLDataWarehouse, Snowflake, Teradata, Vertica

SELECT row_number() OVER (ORDER BY AUTHOR.ID)
FROM AUTHOR

ASE, Access, Aurora MySQL, Derby, HSQLDB, Redshift

/* UNSUPPORTED */

The jOOQ User Manual SQL building SQL Statements (DML) The SELECT statement WINDOW clause

previous : next