Oracle AI Database 26ai introduces the QUALIFY clause — a cleaner way to filter the results of analytic functions without a subquery or Common Table Expression (WITH clause).

The problem

Analytic functions such as RANK(), ROW_NUMBER(), and AVG() OVER (...) are evaluated after the WHERE clause. This means you cannot reference them directly in a WHERE condition — Oracle will error if you try.

The traditional fix is to wrap the query in an inline subquery, so the analytic result exists before the outer WHERE filters it:

-- Find the highest-paid employee in each department
-- A subquery wrapper is the only way to filter on the RANK() result
SELECT employee_id, first_name, last_name, department_id, salary, dept_rank
FROM (
  SELECT employee_id,
         first_name,
         last_name,
         department_id,
         salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
  FROM   employees
)
WHERE dept_rank = 1;

The alternative is a CTE (WITH clause), which is more readable but still adds a layer of indirection:

WITH ranked AS (
  SELECT employee_id,
         first_name,
         last_name,
         department_id,
         salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
  FROM   employees
)
SELECT *
FROM   ranked
WHERE  dept_rank = 1;

Both work. Neither is ideal.

Why WHERE can’t do this

The SQL execution order explains the restriction:

FROM → WHERE → GROUP BY → HAVING → WINDOW → QUALIFY → DISTINCT → ORDER BY → FETCH FIRST

WHERE runs before window functions are computed — so there is nothing to filter yet. QUALIFY runs after, which is exactly where the result lives.

The 26ai solution

QUALIFY lets you filter on analytic results directly in the same query, with no wrapping required. You can reference either a SELECT-list alias or write the window function inline in the QUALIFY predicate — both forms are valid.

-- Highest-paid employee per department — flat, single-level query
SELECT employee_id,
       first_name,
       last_name,
       department_id,
       salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM   employees
QUALIFY dept_rank = 1;

One level. No wrapping. The alias dept_rank is resolved before QUALIFY evaluates it.

Employees above their department average

-- Employees earning above their department's average — no subquery needed
SELECT employee_id,
       first_name,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM   employees
QUALIFY salary > dept_avg;

Here the window function is referenced by alias in QUALIFY. You could equally write QUALIFY salary > AVG(salary) OVER (PARTITION BY department_id) directly — the alias form is generally cleaner.

Most recently hired per job role

A common deduplication pattern — keep the latest record per group — is where QUALIFY really earns its place:

-- Most recently hired employee per job role
SELECT employee_id,
       first_name,
       last_name,
       job_id,
       hire_date,
       ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY hire_date DESC) AS rn
FROM   employees
QUALIFY rn = 1;