Code Lab BetaBUILD. RUN. DISCOVER.
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
Loading…
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
Loading…
Code Lab is in development. New lessons and improvements are on the way. Progress can be deleted.
SQL / POSTGRESQL / Learn / LESSON 06
ROW_NUMBER() OVER (ORDER BY ...) numbers rows 1, 2, 3 … with no gaps and no equal numbers. The order inside OVER decides which row is first. When ORDER BY values tie, the order among them is arbitrary — so add a unique second key: ORDER BY total DESC, id ASC.
With PARTITION BY, numbering restarts in each group: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, id). The "first order of a customer" is then the row numbered 1. A window function cannot appear in WHERE, so wrap the query in a subquery or CTE and filter rn = 1.
ORDER BY inside OVER does not order the output. If you want rows displayed in order, add a final ORDER BY.
Common mistakes: ordering only by a column with repeats; WHERE rn = 1 in the same SELECT; a missing final ORDER BY; confusing ROW_NUMBER with RANK.
Small example:
SELECT id, total, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM orders ORDER BY rn;
Working tables for this task. Grading also uses other datasets with the same schema.
| id | name | category | price |
|---|---|---|---|
| 1 | Atlas | books | 30 |
| 2 | Novel | books | 15 |
| 3 | Comic | books | 14.99 |
| 4 | Lamp | home | 29.99 |
| 5 | Vase | home | 30 |
| 6 | Rug | home | 45 |
| 7 | Hammer | tools | 15 |
| 8 | Drill | tools | 60 |
| 9 | Tape | tools | 15 |
| 10 | Candle | NULL | 8.5 |
| 11 | Gift Card | NULL | 25 |
| id | name | city |
|---|---|---|
| 1 | Mira | Sofia |
| 2 | Niko | Varna |
| 3 | Iva | Sofia |
| 4 | Alex | NULL |
| 5 | Boris | Plovdiv |
| 6 | Lena | Varna |
| 7 | Petya | Sofia |
| id | customer_id | total | order_date |
|---|---|---|---|
| 1 | 1 | 120 | 2024-01-05 |
| 2 | 1 | 35.5 | 2024-01-20 |
| 3 | 2 | 40 | 2024-01-20 |
| 4 | 3 | 99.99 | 2024-01-31 |
| 5 | NULL | 60 | 2024-02-01 |
| 6 | 1 | 40 | 2024-02-01 |
| 7 | 2 | 75.25 | 2024-02-14 |
| 8 | 3 | 100 | 2024-02-15 |
| 9 | 5 | 22 | 2024-02-15 |
| 10 | 1 | 39.99 | 2024-02-29 |
| 11 | 2 | 55 | 2024-03-01 |
| 12 | 3 | 18 | 2024-03-10 |
| 13 | 1 | 130 | 2024-03-15 |
| 14 | 2 | 40 | 2024-03-15 |
| 15 | 2 | 12.5 | 2024-03-31 |
| 16 | 3 | 47 | 2024-04-01 |
Expected columns: id, total, rn · row order is checked
Expected result (displayed data):
Loading…ROW_NUMBER() OVER (ORDER BY ...) numbers rows 1, 2, 3 … with no gaps and no equal numbers. The order inside OVER decides which row is first. When ORDER BY values tie, the order among them is arbitrary — so add a unique second key: ORDER BY total DESC, id ASC. With PARTITION BY, numbering restarts in each group: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, id). The "first order of a customer" is then the row numbered 1. A window function cannot appear in WHERE, so wrap the query in a subquery or CTE and filter rn = 1. ORDER BY inside OVER does not order the output. If you want rows displayed in order, add a final ORDER BY. Common mistakes: ordering only by a column with repeats; WHERE rn = 1 in the same SELECT; a missing final ORDER BY; confusing ROW_NUMBER with RANK.
QUERY STANDBY
›