Available in versions: Dev (3.16) | Latest (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();

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo