Window Functions: Ranking Ties, and Why WHERE Can't See Them

Three ranking functions, one three-way tie, three different results — and none of them can be filtered where you’d first reach for it.

RANK, partitioned per customer

SELECT customer_id, order_id, order_date,
       RANK() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank
FROM orders
WHERE customer_id IN (1,2)
ORDER BY customer_id, order_date;

customer_id order_id    order_date                             order_rank
----------- ----------- -------------------------------------- --------------------
          1        6001            2024-11-15 17:29:30.7797029                    1
          1           1            2024-11-21 17:29:22.6724918                    2
          1        2001            2024-12-28 17:29:25.3388953                    3
          1        4001            2025-01-03 17:29:28.2597329                    4
          2           2            2025-05-19 17:29:22.6765709                    1

PARTITION BY customer_id resets the ranking for each customer — customer 1’s four orders rank 1-4 by date, and customer 2 starts fresh at 1 again, all in a single query with no self-join or subquery per customer.

The bug: filtering the window function’s own alias

SELECT customer_id, order_id,
       RANK() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank
FROM orders
WHERE order_rank = 1;

Msg 207, Level 16, State 1, Server ..., Line 1
Invalid column name 'order_rank'.

order_rank isn’t a real error about window functions specifically — WHERE is evaluated before SELECT in SQL’s logical processing order, and the window function (along with its alias) is computed as part of SELECT. As far as WHERE is concerned, order_rank genuinely doesn’t exist yet at the point it runs.

The fix: filter the CTE’s already-computed output

WITH ranked AS (
    SELECT customer_id, order_id, order_date,
           RANK() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_rank
    FROM orders
)
SELECT TOP 5 * FROM ranked WHERE order_rank = 1 ORDER BY customer_id;

customer_id order_id    order_date                             order_rank
----------- ----------- -------------------------------------- --------------------
          1        6001            2024-11-15 17:29:30.7797029                    1
          2           2            2025-05-19 17:29:22.6765709                    1
          3        2003            2025-04-19 17:29:25.3430459                    1
          4           4            2024-09-23 17:29:22.6806523                    1
          5        4005            2024-11-05 17:29:28.2638375                    1

The CTE (ranked) runs to completion first, alias and all, as its own step — the outer SELECT ... WHERE order_rank = 1 then filters that already-materialized result, where the alias genuinely exists. This finds each customer’s very first order in one query.

RANK vs DENSE_RANK vs ROW_NUMBER on a real tie

SELECT price,
       ROW_NUMBER() OVER (ORDER BY price DESC) AS rn,
       RANK()       OVER (ORDER BY price DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY price DESC) AS drnk
FROM books WHERE price >= 49.90 ORDER BY price DESC;

price    rn                   rnk                  drnk
-------- -------------------- -------------------- --------------------
   49.99                    1                    1                    1
   49.99                    2                    1                    1
   49.99                    3                    1                    1
   49.97                    4                    4                    2
   49.95                    5                    5                    3

Three books genuinely tied at 49.99. ROW_NUMBER doesn’t care about the tie at all — 1, 2, 3, arbitrary but distinct. RANK gives all three 1, then jumps straight to 4 for the next price (skipping by the tie count). DENSE_RANK also gives all three 1, but the next distinct price is 2 — no gap at all. Same data, three genuinely different answers to “what rank is this.”

Practice

WITH ranked AS (
    SELECT price, DENSE_RANK() OVER (ORDER BY price DESC) AS drnk FROM books
)
SELECT DISTINCT price FROM ranked WHERE drnk = 2;

price
--------
   49.97

SELECT CAST(order_date AS DATE) AS day, COUNT(*) AS orders_that_day,
       SUM(COUNT(*)) OVER (ORDER BY CAST(order_date AS DATE)) AS running_total
FROM orders
GROUP BY CAST(order_date AS DATE)
ORDER BY day
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

day              orders_that_day running_total
---------------- --------------- -------------
      2024-09-14              11            11
      2024-09-15              11            22
      2024-09-16              11            33

SELECT TOP 3 customer_id, order_id,
       COUNT(*) OVER (PARTITION BY customer_id) AS total_orders_by_customer
FROM orders WHERE customer_id = 1;

customer_id order_id    total_orders_by_customer
----------- ----------- ------------------------
          1           1                        4
          1        2001                        4
          1        4001                        4

Next: recursive CTEs — walking a hierarchy without knowing its depth in advance.