PARTITION BY criterion

Part of the Fundamentals section of Coddy's SQL journey — lesson 59 of 72.

Another option for the OVER () clause is 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 ORDER BY id) as row_num
FROM table1

This 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 when ordered by id.

Note: ROW_NUMBER() requires an ORDER BY clause within the OVER() to determine how rows should be numbered within each partition.

We can even specify multiple columns inside the PARTITION BY:

ROW_NUMBER() OVER (PARTITION BY type, hue ORDER BY id)

Challenge

Easy

Available tables and columns:
doors: id, publication_year
doors_specs: id, country, color

A factory is building doors. It needs to number the doors for each country and color combination that have a publication_year smaller than 2000. Name this column row_num.

The doors should be numbered within each group in ascending order by their id. Doors without specs should be ignored. Sort the final result in ascending order by the id.

Cheat sheet

Use PARTITION BY in the OVER()</clause to number rows within groups: <pre><code class="language-sql">SELECT id, type, ROW_NUMBER() OVER (PARTITION BY type) as row_num FROM table1

You can partition by multiple columns:

ROW_NUMBER() OVER (PARTITION BY type, hue)

Try it yourself

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

PARTITION BY criterion

Cheat sheet

All lessons in Fundamentals