SQL ROW_NUMBER: Number Rows Within Each Customer

A grouped query combines detail rows. A window function can calculate across related rows while keeping the detail visible. ROW_NUMBER is a useful first example because it assigns a sequence number within a defined order.

Practice setup: Use the fictional database in the SQL practice lab. Run that setup once in a fresh SQLite practice database. Customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional units. The examples in this series use SQLite syntax; check your database's documentation before adapting them elsewhere.

Number all orders by amount

SELECT order_id, customer_id, amount,
       ROW_NUMBER() OVER (
         ORDER BY amount DESC, order_id
       ) AS position
FROM orders
ORDER BY position;

Order 104 gets position 1, 101 gets 2, 102 gets 3 and 103 gets 4. OVER defines the window calculation. Its ORDER BY determines the numbering, while the final ORDER BY determines how the query result is displayed.

Keep both roles in mind. The calculation's order does not remove the need for a final presentation order. A report should ask explicitly for the order its reader needs.

Restart the numbering for each customer

SELECT order_id, customer_id, amount,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY amount DESC, order_id
       ) AS customer_position
FROM orders
ORDER BY customer_id, customer_position;

Customer 1's order 101 is position 1 and order 103 is position 2. Customers 2 and 3 each have one order at position 1. PARTITION BY creates separate calculation groups without collapsing the rows into a GROUP BY summary.

Keep one top order per customer

A CTE makes the calculated position available for an outer filter:

WITH numbered AS (
  SELECT order_id, customer_id, amount,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY amount DESC, order_id
         ) AS customer_position
  FROM orders
)
SELECT order_id, customer_id, amount
FROM numbered
WHERE customer_position = 1
ORDER BY customer_id;

The answer is orders 101, 102 and 104. The query returns one order per customer who has at least one order. It does not create a row for a customer absent from orders.

Decide how ties should behave

ROW_NUMBER always assigns different sequence numbers. The order_id tie-breaker makes the chosen row predictable when two amounts are equal. If the requirement is to include all orders tied for the largest amount, a ranking function such as RANK or a maximum-based comparison may be more appropriate.

Do not call this sequence a permanent rank stored in the table. It is calculated for this query and changes when the input or ordering changes. Also, an order ID is not automatically a timestamp; our amount ranking does not claim to identify the latest order.

Try it yourself

Change the per-customer ordering to amount ascending, with order_id as a tie-breaker. Return position 1 from each partition. Which order changes compared with the largest-order result?

WITH numbered AS (
  SELECT order_id, customer_id, amount,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY amount ASC, order_id
         ) AS customer_position
  FROM orders
)
SELECT order_id, customer_id, amount
FROM numbered
WHERE customer_position = 1
ORDER BY customer_id;

Keep exploring: SQL learning path.