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

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

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo