|The jOOQ User Manual : SQL building : SQL Statements (DML) : The SELECT statement : QUALIFY clause||previous : next|
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();