Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
SELECT DISTINCT ON
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A useful, though perhaps a bit esoteric PostgreSQL specific extension to SELECT DISTINCT
is the ON
clause. Using this clause, PostgreSQL users can specify a distinctness criteria, but then produce other columns per distinct group from one of the group's tuples. This is normally not possible in SQL, but with ON
, the first tuple in the group according to the ORDER BY
clause can be accessed nonetheless. An example:
SELECT DISTINCT ON (BOOK.LANGUAGE_ID) BOOK.LANGUAGE_ID, BOOK.TITLE FROM BOOK ORDER BY BOOK.LANGUAGE_ID, BOOK.TITLE
Select<?> select1 = create.select(BOOK.LANGUAGE_ID, BOOK.TITLE) .distinctOn(BOOK.LANGUAGE_ID) .from(BOOK) .orderBy(BOOK.LANGUAGE_ID, BOOK.TITLE).fetch();
For syntactic reasons, the order of keywords had to be inversed as the PostgreSQL syntax cannot be easily reproduced in jOOQ's internal DSL. Quite likely, you might find jOOQ's syntax a bit more intuitive, though, as it more clearly separates the SELECT
parts and the DISTINCT ON
parts. Arguably, the DISTINCT ON
clause should be positioned after ORDER BY
, where it logically belongs.
Dialect support
This example using jOOQ:
select(BOOK.LANGUAGE_ID, BOOK.TITLE).distinctOn(BOOK.LANGUAGE_ID).from(BOOK).orderBy(BOOK.LANGUAGE_ID, BOOK.TITLE)
Translates to the following dialect specific expressions:
Aurora Postgres, ClickHouse, CockroachDB, DuckDB, H2, Postgres, YugabyteDB
SELECT DISTINCT ON (BOOK.LANGUAGE_ID) BOOK.LANGUAGE_ID, BOOK.TITLE FROM BOOK ORDER BY BOOK.LANGUAGE_ID, BOOK.TITLE
BigQuery, DB2, Databricks, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica
SELECT t.LANGUAGE_ID, t.TITLE FROM ( SELECT BOOK.LANGUAGE_ID, BOOK.TITLE, row_number() OVER ( PARTITION BY BOOK.LANGUAGE_ID ORDER BY BOOK.LANGUAGE_ID, BOOK.TITLE ) rn FROM BOOK ) t WHERE rn = 1 ORDER BY LANGUAGE_ID, TITLE
ASE, Access, Aurora MySQL, Derby, HSQLDB
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!