Analytic functions have been among my favorite Oracle SQL features ever since their introduction in Oracle 8i. I’ve used them extensively over the years, and I still remember how much easier they made many SQL problems that previously required rather complicated solutions.

Tom Kyte once described analytic functions as “the coolest thing to happen to the SQL language since the introduction of the keyword SELECT.” I couldn’t agree more.

But there was one thing I kept missing: the ability to filter directly on the results of analytic functions. With the new QUALIFY clause in Oracle 26ai, this is finally possible. It’s a relatively small addition to Oracle SQL, but a long-awaited and very welcome one.

Let’s see how it works – and then take a look at something rather surprising under the hood.

Continue reading →