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
QUALIFY clause
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A select few dialects support a very useful QUALIFY clause, which can be used to filter using window functions without having to nest the window function calculation in a derived table.
For example, if you do not have access to the WITH TIES clause, you could easily emulate it like this. The following query finds the top 5 author WITH TIES, counting their books:
SELECT AUTHOR_ID, count(*) FROM BOOK GROUP BY AUTHOR_ID QUALIFY rank() OVER (ORDER BY count(*) DESC) <= 5 ORDER BY count(*) DESC
create.select(BOOK.AUTHOR_ID, count())
.from(BOOK)
.groupBy(BOOK.AUTHOR_ID)
.qualify(rank().over(orderBy(count().desc())).le(5))
.orderBy(count().desc())
.fetch();
If your dialect does not support QUALIFY natively, then jOOQ can apply a transformation from QUALIFY to derived tables.
Dialect support
This example using jOOQ:
select(AUTHOR.ID).from(AUTHOR).qualify(rank().over(orderBy(AUTHOR.ID)).le(10))
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, MemSQL, Postgres, SQLite, Trino, Vertica, YugabyteDB
SELECT t.ID ID
FROM (
SELECT
*,
(rank() OVER (ORDER BY AUTHOR.ID) <= 10) w0
FROM AUTHOR
) t
WHERE w0
BigQuery
SELECT AUTHOR.ID FROM AUTHOR WHERE TRUE QUALIFY rank() OVER (ORDER BY AUTHOR.ID) <= 10
ClickHouse, Databricks, DuckDB, Exasol, H2, Snowflake, Teradata
SELECT AUTHOR.ID FROM AUTHOR QUALIFY rank() OVER (ORDER BY AUTHOR.ID) <= 10
DB2, SQLDataWarehouse, SQLServer, Sybase
SELECT t.ID ID
FROM (
SELECT
*,
CASE
WHEN rank() OVER (ORDER BY AUTHOR.ID) <= 10 THEN 1
WHEN NOT (rank() OVER (ORDER BY AUTHOR.ID) <= 10) THEN 0
END w0
FROM AUTHOR
) t
WHERE w0 = 1
Firebird
SELECT t.ID ID
FROM (
SELECT
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
AUTHOR.DATE_OF_BIRTH,
AUTHOR.YEAR_OF_BIRTH,
AUTHOR.DISTINGUISHED,
CASE
WHEN rank() OVER (ORDER BY AUTHOR.ID) <= 10 THEN 1
WHEN NOT (rank() OVER (ORDER BY AUTHOR.ID) <= 10) THEN 0
END w0
FROM AUTHOR
) t
WHERE w0 = 1
Hana
SELECT t.ID ID
FROM (
SELECT
*,
CASE
WHEN rank() OVER (ORDER BY AUTHOR.ID) <= 10 THEN TRUE
WHEN NOT (rank() OVER (ORDER BY AUTHOR.ID) <= 10) THEN FALSE
END w0
FROM AUTHOR
) t
WHERE w0 = TRUE
Informix
SELECT t.ID ID
FROM (
SELECT
*,
CASE
WHEN rank() OVER (ORDER BY AUTHOR.ID) <= 10 THEN CAST('t' AS boolean)
WHEN NOT (rank() OVER (ORDER BY AUTHOR.ID) <= 10) THEN CAST('f' AS boolean)
END w0
FROM AUTHOR
) t
WHERE w0
MariaDB, MySQL, Oracle
SELECT t.ID ID
FROM (
SELECT
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
AUTHOR.DATE_OF_BIRTH,
AUTHOR.YEAR_OF_BIRTH,
AUTHOR.DISTINGUISHED,
(rank() OVER (ORDER BY AUTHOR.ID) <= 10) w0
FROM AUTHOR
) t
WHERE w0
Redshift
SELECT AUTHOR.ID FROM AUTHOR WHERE 1 = 1 QUALIFY rank() OVER (ORDER BY AUTHOR.ID) <= 10
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!