How to Filter Analytic Results with QUALIFY in Oracle

Top-N-per-group questions are everywhere: the best-paid employee of each department, the latest order of each customer, the three biggest spenders. Analytic functions such as ROW_NUMBER and RANK compute the ranking, but until recently you could not filter on it in the same query block, so every such query needed a subquery. Oracle AI Database 26ai adds the QUALIFY clause, which filters rows after the analytic functions are computed.

Code for This Guide

The main examples are in the examples/analytic-queries folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Why WHERE Cannot Do It

Oracle computes analytic functions after WHERE, GROUP BY, and HAVING, so they do not exist yet when WHERE runs:

Example:

select last_name from employees where row_number() over (order by salary desc) <= 3;

Output:

Error starting at line : 1
In command -
select last_name from employees where row_number() over (order by salary desc) <= 3
Error at Command Line : 1 Column : 39
Error report -
SQL Error: ORA-30483: window  functions are not allowed here

Before 26ai, the fix was to compute the ranking in an inline view and filter it in the outer query.

Example:

-- the same result before QUALIFY: an inline view, filtered outside
select department_id, last_name, salary
from   (select department_id, last_name, salary,
               row_number() over (partition by department_id order by salary desc) as rn
        from   employees)
where  rn = 1
and    department_id in (10, 20, 30)
order  by department_id;

Output:

   DEPARTMENT_ID LAST_NAME       SALARY
________________ ____________ _________
              10 Haddad           48000
              20 Clarke           27000
              30 Rahman           22000

Syntax

QUALIFY takes a condition on analytic functions and comes after HAVING (and after a WINDOW clause, if there is one), before ORDER BY.

Syntax:

select ...
from   ...
[where  ...]
[group  by ... [having ...]]
qualify condition
[order  by ...]

The order of evaluation becomes: WHERE filters rows, GROUP BY and HAVING form and filter groups, the analytic functions are computed, QUALIFY filters on them, and ORDER BY sorts.

Top One per Group

The same question in one query block: number the employees of each department by salary, highest first, and keep number 1.

Example:

-- the best-paid employee of each department, without a subquery
select department_id, last_name, salary
from   employees
qualify row_number() over (partition by department_id order by salary desc) = 1
order  by department_id;

Output:

   DEPARTMENT_ID LAST_NAME       SALARY
________________ ____________ _________
              10 Haddad           48000
              20 Clarke           27000
              30 Rahman           22000
              40 Evans            34000
              50 Mehta            33000
              60 Okafor           29000
              70 Fernández        23000
              80 Smith            15800
              90 Chen             12000
             100 Nguyen           21500

10 rows selected.

The analytic function appears only in QUALIFY; it does not need to be in the select list.

Keep Ties with DENSE_RANK

ROW_NUMBER picks exactly one row per group, choosing arbitrarily among ties. RANK and DENSE_RANK give tied rows the same number, so filtering on them keeps all ties. This query keeps the two most recent hire dates in each department.

Example:

-- the two most recent hires of each department, keeping ties
select department_id, last_name, hire_date
from   employees
where  department_id in (20, 30)
qualify dense_rank() over (partition by department_id order by hire_date desc) <= 2
order  by department_id, hire_date desc;

Output:

   DEPARTMENT_ID LAST_NAME    HIRE_DATE
________________ ____________ ______________
              20 Rossi        01-OCT-2024
              20 Mensah       17-APR-2023
              30 O'Brien      25-AUG-2025
              30 Brown        05-FEB-2024

Combine with WHERE and GROUP BY

QUALIFY works on grouped queries too, where the analytic function can rank the aggregates. This query drops cancelled bookings with WHERE, totals the rest per customer with GROUP BY, ranks the totals, and keeps the top three with QUALIFY.

Example:

-- the three customers who spent the most, with WHERE, GROUP BY, and QUALIFY together
select customer_id, sum(total_amount) as spent,
       rank() over (order by sum(total_amount) desc) as spend_rank
from   bookings
where  status <> 'CANCELLED'
group  by customer_id
qualify rank() over (order by sum(total_amount) desc) <= 3
order  by spend_rank;

Output:

   CUSTOMER_ID       SPENT    SPEND_RANK
______________ ___________ _____________
            12    38151.68             1
            85    37212.89             2
             5    34865.31             3

QUALIFY Compared with HAVING and FETCH FIRST

For a single top-N over the whole result, FETCH FIRST n ROWS ONLY is simpler. QUALIFY is the tool when the ranking restarts for every group.

Related Guides

• How to Define Window Frames in Oracle Analytic Queries
• How to Filter Groups with HAVING in Oracle SQL
• How to Use Subqueries in Oracle SQL

Conclusion

QUALIFY, new in Oracle AI Database 26ai, filters rows after analytic functions are computed, just as HAVING filters groups after aggregation. Use it with ROW_NUMBER for one row per group, with RANK or DENSE_RANK to keep ties, and together with WHERE and GROUP BY to rank aggregates, all without the inline view that top-N-per-group queries used to need.