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.
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!