SQL window functions and ranking: ROW_NUMBER, RANK, and DENSE_RANK

Calculating across rows without losing them

In the aggregation article, we learned how GROUP BY summarizes several rows into one result row. Consider the following orders table, where the value of each order is quantity * unit_price:

Suppose we want the total order value for each customer. We can use GROUP BY:

SELECT
    customer,
    SUM(quantity * unit_price) AS total_order_value
FROM orders
GROUP BY customer
ORDER BY customer;

The six original order rows have become four customer rows. That is exactly what aggregation is supposed to do. But now consider a different question: show every individual order, but also rank the orders from highest to lowest value. We still need every order row. We do not want six rows collapsed into one summary; we want six rows to remain six rows, with an additional calculated rank value. This is what window functions are designed for.

GROUP BY vs window functions

GROUP BY changes the grain of the result, producing one row per customer, and the individual order rows disappear into the summary. A window function keeps the individual rows:

SELECT
    order_id,
    customer,
    quantity * unit_price AS order_amount,
    ROW_NUMBER() OVER (
        ORDER BY quantity * unit_price DESC
    ) AS position
FROM orders;

Every order still exists. The window function simply adds information calculated by looking at other rows.

What does OVER mean?

In ROW_NUMBER() OVER (ORDER BY quantity * unit_price DESC), ROW_NUMBER() is the window function and OVER (...) tells SQL to calculate this function across a set of related rows. That set of rows is called the window. Here it uses all rows in the query result and orders them from highest order amount to lowest, and then ROW_NUMBER() gives each row a position:

Orders  --order by amount descending-->   8500   --ROW_NUMBER-->   1
                                          3600                    2
                                          3600                    3
                                          3200                    4
                                          1500                    5
                                           750                    6

The OVER clause is what turns ROW_NUMBER() into a calculation across rows.

ORDER BY inside OVER

The ORDER BY inside a window definition determines how the window function should evaluate the rows. ORDER BY quantity * unit_price DESC means number rows from the largest order amount to the smallest; changing it to ASC reverses the logic. The ordering is part of the analytical calculation.

Window ORDER BY and final ORDER BY are different

These two are not the same clause. Consider a query that ranks by amount inside OVER but displays by order_id:

SELECT
    order_id,
    customer,
    quantity * unit_price AS order_amount,
    ROW_NUMBER() OVER (
        ORDER BY quantity * unit_price DESC
    ) AS amount_position
FROM orders
ORDER BY order_id;

ORDER BY inside OVER    controls the window calculation
Final ORDER BY          controls how result rows are displayed

ROW_NUMBER, RANK, and DENSE_RANK

ROW_NUMBER() assigns consecutive numbers, and every row receives a different number. Notice the tie between Carla and Ben at 3600: ROW_NUMBER() still gives them different numbers (2 and 3), because it must assign one row number to each row.

There is another issue with the two 3600 rows: ROW_NUMBER() OVER (ORDER BY quantity * unit_price DESC) does not specify which one becomes 2 and which becomes 3. If the assignment must be predictable, add a tie-breaker:

ROW_NUMBER() OVER (
    ORDER BY
        quantity * unit_price DESC,
        order_id ASC
)

Now 3600 | order 1003 comes before 3600 | order 1006 because 1003 < 1006. This is the same deterministic ordering principle we learned in the Top-N article.

RANK: give tied rows the same rank

If the requirement is “rank orders by value, and orders with the same value should have the same rank,” use RANK():

There is no rank 3. Two rows occupied rank 2, so the next row receives rank 4. You can think of the positions as 1, 2, 2, 4, 5, 6. This is the defining behaviour of RANK().

DENSE_RANK: rank ties without gaps

DENSE_RANK() also gives equal values the same rank, but it does not leave gaps afterward:

The ranking becomes 1, 2, 2, 3, 4, 5 instead of 1, 2, 2, 4, 5, 6.

ROW_NUMBER vs RANK vs DENSE_RANK

Using the same values, the three functions produce:

ROW_NUMBER   1, 2, 3, 4, 5, 6   every row a unique number
RANK         1, 2, 2, 4, 5, 6   ties share a rank, gaps remain
DENSE_RANK   1, 2, 2, 3, 4, 5   ties share a rank, no gaps

Using RANK when you really mean the top distinct values

Suppose the requirement is return orders belonging to the top three distinct order amounts. The distinct amounts are 8500, 3600, 3200, 1500, 750, so the top three are 8500, 3600, 3200. Someone computes RANK() OVER (ORDER BY quantity * unit_price DESC), getting 8500 -> 1, 3600 -> 2, 3600 -> 2, 3200 -> 4, and then keeps rank <= 3.

-- Option A: Yes, 3200 is the third-highest distinct amount
-- Option B: No, RANK() assigns it rank 4
-- Option C: SQL automatically changes its rank to 3
-- Option D: The query fails because ranks contain duplicates

PARTITION BY: restart the calculation for each group

So far, every order has been compared with every other order. But analytical questions often say “rank orders within each status” or “number orders separately for each customer.” This is what PARTITION BY does. To rank orders by amount within each status:

SELECT
    order_id,
    customer,
    status,
    quantity * unit_price AS order_amount,
    RANK() OVER (
        PARTITION BY status
        ORDER BY quantity * unit_price DESC
    ) AS rank_within_status
FROM orders
ORDER BY
    status,
    rank_within_status,
    order_id;

The ranking restarts for each status. Think of PARTITION BY status as temporarily dividing the rows into independent sets: the paid partition ranks 1, 1, 3, 4, the pending partition 1, and the cancelled partition 1. Then SQL places the calculated values back beside the original rows. The rows are not collapsed, which is a crucial difference from GROUP BY status.

PARTITION BY is not GROUP BY

GROUP BY status with COUNT(*) produces one row per status (paid = 4, pending = 1, cancelled = 1). ROW_NUMBER() OVER (PARTITION BY status ORDER BY order_id) keeps all six orders; PARTITION BY only controls which rows the window function considers together.

GROUP BY        Put rows into groups and collapse each group.
PARTITION BY    Put rows into groups for the calculation, but keep every row.

Ranking within each customer

Another common question: number each customer’s orders from highest value to lowest.

SELECT
    order_id,
    customer,
    quantity * unit_price AS order_amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer
        ORDER BY
            quantity * unit_price DESC,
            order_id ASC
    ) AS customer_order_number
FROM orders
ORDER BY
    customer,
    customer_order_number;

Numbering starts again for each customer. This pattern is extremely useful for problems such as the latest order per customer, the most expensive product per category, the top three transactions per account, the first event per user, or the best-performing item per region.

Return the top N rows within each group

One of the most useful ranking patterns is returning the top N rows within each group. To return each customer’s highest-value order, first assign row numbers, then keep row number 1:

WITH ranked_orders AS (
    SELECT
        order_id,
        customer,
        quantity * unit_price AS order_amount,
        ROW_NUMBER() OVER (
            PARTITION BY customer
            ORDER BY
                quantity * unit_price DESC,
                order_id ASC
        ) AS rn
    FROM orders
)
SELECT
    order_id,
    customer,
    order_amount
FROM ranked_orders
WHERE rn = 1
ORDER BY customer;

The logic has two stages: stage 1 ranks orders within each customer, and stage 2 keeps row number 1. Window functions often work naturally with an intermediate query.

Why not put ROW_NUMBER directly in WHERE?

A tempting query filters WHERE ROW_NUMBER() OVER (...) = 1 directly. In many SQL databases, this is not allowed: the WHERE filtering step occurs before window-function results are available for that query level. So the common pattern is to calculate the window function in a subquery or CTE, then filter the calculated result:

Calculate the window function
        v
Subquery or CTE
        v
Filter the calculated result

Some database systems provide additional syntax such as QUALIFY for filtering window results directly, but it is not universal.

How to spot window-function problems

Think about window functions when the requirement contains phrases such as:

Watch for these mistakes:

• GROUP BY is used even though individual rows must remain.
• ROW_NUMBER is used when ties should share a rank.
• RANK is used when the requirement means top N distinct values.
• ROW_NUMBER has ties but no deterministic tie-breaker.
• PARTITION BY uses the wrong grouping column.
• ORDER BY inside OVER is confused with the final ORDER BY.
• A window-function alias is filtered directly in WHERE at the same query level.

Break a ranking window into three questions

Consider ROW_NUMBER() OVER (PARTITION BY customer ORDER BY quantity * unit_price DESC, order_id ASC):

One more prediction

Consider these order amounts. Requirement: assign equal amounts the same rank, and the next distinct amount should receive the next consecutive rank. Which function should you use?

-- Option A
ROW_NUMBER()

-- Option B
RANK()

-- Option C
DENSE_RANK()

-- Option D
COUNT()

Summary

Window functions let SQL calculate across related rows without collapsing those rows, which is the central difference from aggregation: GROUP BY turns many rows into fewer summary rows, while a window function keeps the same rows and adds analytical values. The OVER clause defines the window, and PARTITION BY creates independent groups inside that calculation. The ranking functions differ mainly in how they handle ties: ROW_NUMBER() gives every row a unique number (1, 2, 3, 4); RANK() lets ties share a rank and creates gaps (1, 2, 2, 4); DENSE_RANK() lets ties share a rank without gaps (1, 2, 2, 3).

Next, we will build on this foundation with running totals, moving averages, and window frames.