If you’ve ever written analytics queries with RANK(), DENSE_RANK(), ROW_NUMBER(), AVG() OVER(...), etc., you know the pain:

• you compute a window value in the SELECT
• then you can’t reference it in WHERE
• so you wrap everything in a subquery… just to filter on that computed value

Oracle Database 26ai fixes this nicely with QUALIFY.

SELECT
  ...
  RANK() OVER (ORDER BY revenue DESC) AS rev_rank
FROM test_tbl
QUALIFY rev_rank <= 5
ORDER BY rev_rank, name;

QUALIFY is evaluated after window functions are computed, so you can filter on them directly — no nesting needed.

Lets create test data and do some tests

DROP TABLE IF EXISTS test_tbl CASCADE CONSTRAINTS;

CREATE TABLE test_tbl
(
  id   NUMBER,
  name VARCHAR2(30) NOT NULL,
  val1 NUMBER       NOT NULL,
  val2 NUMBER       NOT NULL,
  val3 NUMBER       NOT NULL
);

INSERT INTO test_tbl VALUES (1, 'Name1', 100, 10, 20);
INSERT INTO test_tbl VALUES (2, 'Name2', 100, 10, 30);
INSERT INTO test_tbl VALUES (3, 'Name3', 100, 20, 30);
INSERT INTO test_tbl VALUES (4, 'Name4', 100, 20, 30);
INSERT INTO test_tbl VALUES (5, 'Name5', 200, 30, 30);
INSERT INTO test_tbl VALUES (6, 'Name6', 200, 30, 30);
INSERT INTO test_tbl VALUES (7, 'Name7', 200, 40, 30);
INSERT INTO test_tbl VALUES (8, 'Name8', 200, 40, 20);

COMMIT;

Check what we have inside the table with some analytical functions

SELECT
id, name, val1, val2,
RANK() OVER (ORDER BY val1 DESC) AS val1_rank,
RANK() OVER (ORDER BY val2 DESC) AS val2_rank,
RANK() OVER (ORDER BY val3 DESC) AS val3_rank
FROM test_tbl
ORDER BY 1;

ID NAME   VAL1  VAL2  VAL3  VAL1_RANK  VAL2_RANK  VAL3_RANK
-- ----- ----- ----- ----- ---------- ---------- ----------
 1 Name1   100    10    20          5          7          7 
 2 Name2   100    10    30          5          7          1
 3 Name3   100    20    30          5          5          1
 4 Name4   100    20    30          5          5          1
 5 Name5   200    30    30          1          3          1
 6 Name6   200    30    30          1          3          1
 7 Name7   200    40    30          1          1          1
 8 Name8   200    40    20          1          1          7

To filter records by columns with analytical functions you had to use before 26ai extra query

SELECT * FROM (
  SELECT
    id, name, val1, val2, val3,
    RANK() OVER (ORDER BY val1 DESC) AS val1_rank,
    RANK() OVER (ORDER BY val2 DESC) AS val2_rank,
    RANK() OVER (ORDER BY val3 DESC) AS val3_rank
   FROM test_tbl
)
WHERE val1_rank> 1 AND val3_rank < 2
ORDER BY 1;

 ID NAME   VAL1  VAL2  VAL3  VAL1_RANK  VAL2_RANK  VAL3_RANK
-- ------ ----- ----- ----- ---------- ---------- ----------
 2 Name2    100    10    30          5          7          1
 3 Name3    100    20    30          5          5          1
 4 Name4    100    20    30          5          5          1

New solution is very elegant

SELECT
  id, name, val1, val2, val3,
  RANK() OVER (ORDER BY val1 DESC) AS val1_rank,
  RANK() OVER (ORDER BY val2 DESC) AS val2_rank,
  RANK() OVER (ORDER BY val3 DESC) AS val3_rank
FROM test_tbl
QUALIFY val1_rank> 1 AND val3_rank < 2
ORDER BY 1

 ID NAME   VAL1  VAL2  VAL3  VAL1_RANK  VAL2_RANK  VAL3_RANK
-- ------ ----- ----- ----- ---------- ---------- ----------
 2 Name2    100    10    30          5          7          1
 3 Name3    100    20    30          5          5          1
 4 Name4    100    20    30          5          5          1

Execution order (mental model)

When you read a query with QUALIFY, this is the useful “pipeline”:

FROM test_tbl                   ← Get rows
WHERE ... (if present)          ← Filter base rows
... OVER(...)                   ← Compute window functions
QUALIFY ...                     ← Filter using those results
ORDER BY ...                    ← Sort final output

Have a fun 🙂

  Tomasz