Number Rows in Groups
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 11 of 25.
Window functions calculate across related rows without collapsing them. ROW_NUMBER() OVER (...) assigns successive numbers. PARTITION BY restarts numbering for each group; the window ORDER BY determines the numbering order.
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
SELECT id, customer_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY id) AS position FROM orders ORDER BY id;Ari orders 11 and 12 receive positions 1 and 2. Every order remains in the result. The outer ORDER BY controls display, while the ORDER BY inside OVER controls the calculation.
Use ROW_NUMBER with a stable tie-breaker; window order and final display order have separate jobs.
Challenge
MediumReturn id, customer_id and newest_position, numbering each customer orders newest first by order date, then id descending. Display rows by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
3Window Calculations
Number Rows in GroupsRanks and TiesRunning TotalsCompare with LAGLatest Order per CustomerPractice on your own: PostgreSQL playground