PARTITION BY criterion
Lesson 5 of 13 in Coddy's SQL for advanced course.
Another option for the OVER () options are the PARTITION BY
It allows us to number the rows for each group separately
For example:
| id | type |
| 132 | t1 |
| 52 | t2 |
| 92 | t1 |
| 154 | t3 |
| 198 | t1 |
SELECT id, type, ROW_NUMBER() OVER (PARTITION BY type) as row_num
FROM table1This will generate a column that will number each type within itself:
| id | type | row_num |
| 132 | t1 | 1 |
| 52 | t2 | 1 |
| 92 | t1 | 2 |
| 154 | t3 | 1 |
| 198 | t1 | 3 |
id 92 has row_num 2 because it is the second row of type t1
We can also combine PARTITION BY with ORDER BY:
SELECT id, type, ROW_NUMBER() OVER (PARTITION BY type ORDER BY id) as row_num
FROM table1This will number the rows in ascending order by the id of each type:
| id | type | row_num |
| 132 | t1 | 2 |
| 52 | t2 | 1 |
| 92 | t1 | 1 |
| 154 | t3 | 1 |
| 198 | t1 | 3 |
Now id 132 has row_num 2 because it is larger than 92 and smaller than 198.
We can even specify multiple columns inside the PARTITION BY:
ROW_NUMBER() OVER (PARTITION BY type, hue)
Challenge
EasyA factory is building doors. It needs to number the doors for each country and color that their publication_year is before 2000. name this column row_num.
Doors without specs should be ignored. sort the result in ascending order by the id.
Try it yourself
All lessons in SQL for advanced
4Summary
Final challenge #12Window Functions part 1
ROW_NUMBER functionORDER BY criterionPARTITION BY criterionLEAD & LAG functionsRecap challenge #1Recap challenge #23Window Functions part 2
RANK & DENSE_RANK functionsNTILE functionAggregation functionsROWS & RANGE criterion