Oracle AI Database 26ai: Filtering Analytic Function Results with the QUALIFY Clause

Suppose we want to display, from the employees table, only the employee with the highest salary in each department.
Before version 26ai(23.26), we usually had to use a subquery to achieve this result:

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

In version 26ai, you can achieve the same result without using a subquery, thanks to the QUALIFY clause:

SELECT employee_id,
       first_name,
       department_id,
       salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS Rank_Per_DEP
FROM employees
QUALIFY Rank_Per_DEP = 1;

Therefore, the QUALIFY clause is used to filter the results of analytic functions.
It eliminates the need for a subquery and improves both the readability and efficiency of the query.

If you try to use this clause in earlier Oracle versions, you’ll encounter the following error:

ERROR at line 7:
ORA-03049: SQL keyword 'RANK_PER_DEP' is not syntactically valid following
'...FROM employees
QUALIFY '
Help: https://docs.oracle.com/error-help/db/ora-03049/