SQL: ROW_NUMBER numbers rows without collapsing them

A window function computes across a set of rows but returns one value per row, so you keep the detail. GROUP BY collapses; OVER does not.

Code

SELECT customer_id, id, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
  FROM orders;

-- top 3 orders per customer
SELECT * FROM (
  SELECT customer_id, id, total,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) rn
    FROM orders) t
 WHERE rn <= 3;

Output

customer_id | id   | total | rn
          7 | 8821 |  940  |  1
          7 | 8102 |  610  |  2
         12 | 9001 | 1200  |  1

-- The window is applied AFTER WHERE, so you cannot filter on rn in the same
-- SELECT - hence the subquery (or a CTE).

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25