The jOOQ User Manual SQL building Column expressions Window functions FROM FIRST, FROM LAST

previous : next

FROM FIRST, FROM LAST

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The NTH_VALUE window function has an additional clause that allows for specifying whether the Nth value should be searched from the FIRST or the LAST row in the window.

Possible clause values include:

• FROM FIRST: The default.
• FROM LAST: N is counted from the LAST row, backwards.

Dialect support

This example using jOOQ:

nthValue(BOOK.ID, 2).fromLast().over(orderBy(BOOK.ID))

Translates to the following dialect specific expressions:

-- DB2, EXASOL, FIREBIRD, H2, ORACLE, SNOWFLAKE
nth_value(BOOK.ID, 2) FROM LAST OVER (ORDER BY BOOK.ID)

-- ACCESS, ASE, AURORA_MYSQL, AURORA_POSTGRES, BIGQUERY, COCKROACHDB, DERBY, DUCKDB, HANA, HSQLDB, INFORMIX, MARIADB, 
-- MEMSQL, MYSQL, POSTGRES, REDSHIFT, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA, YUGABYTEDB
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website

The jOOQ User Manual SQL building Column expressions Window functions FROM FIRST, FROM LAST

previous : next