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.
Standard SQL equivalence
The PostgreSQL extension isn't really necessary as there is a standard SQL equivalence using ROW_NUMBER
filtering. In the below example, we're using an extension to the standard, the QUALIFY clause, to illustrate:
SELECT BOOK.LANGUAGE_ID, BOOK.TITLE FROM BOOK QUALIFY ROW_NUMBER() OVER (PARTITION BY BOOK.LANGUAGE_ID ORDER BY BOOK.TITLE) = 1 ORDER BY BOOK.LANGUAGE_ID, BOOK.TITLE
Select<?> select1 = create.select(BOOK.LANGUAGE_ID, BOOK.TITLE) .from(BOOK) .qualify(rowNumber().over(partitionBy(BOOK.LANGUAGE_ID).orderBy(BOOK.TITLE)).eq(1)) .orderBy(BOOK.LANGUAGE_ID, BOOK.TITLE).fetch();
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!