Available in versions: Dev (3.20) | Latest (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
DB2, 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, BigQuery, Derby, HSQLDB
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!