PARTITION & ORDER

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

We can also combine PARTITION BY with ORDER BY. For example consider the following table1:

SELECT id, type, ROW_NUMBER() OVER (PARTITION BY type ORDER BY id) as row_num
FROM table1

This will number the rows in ascending order by the id of each type:

Now id 132 has row_num 2 because it is larger than 92 and smaller than 198 (of all the t1 type rows).

Challenge

Easy

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

Write a SQL query to analyze the sequence of door colors within each country, ordered by publication year. For each door space, show:

The country
The color
The publication year
A number representing the order of colors within each country based on publication year. Call this column color_sequence_number

Cheat sheet

You can combine PARTITION BY with ORDER BY to number rows within each partition in a specific order:

SELECT id, type, ROW_NUMBER() OVER (PARTITION BY type ORDER BY id) as row_num
FROM table1

This numbers rows in ascending order by id within each type group. Each partition restarts numbering from 1.

Try it yourself

Test yourself

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