Module 4 · Dates, windows and time Lesson 11 of 15
Window functions: rank without collapsing rows
A window function computes across a set of rows while keeping every row: rank jobs inside each city, or show a row next to its group total. GROUP BY cannot do this because it collapses the rows.
Worked example
ROW_NUMBER() OVER (PARTITION BY city ORDER BY salary DESC) numbers the jobs within each city, so a filter of rnk = 1 gives the best-paid job per city.
Your practice task
List the top two roles by salary for each city, using ROW_NUMBER in a subquery and filtering on the rank.
Do this before moving on. Reading a solution teaches the shape of it; typing it teaches you.
Key takeaway
A window function computes across a set of rows while keeping every row: rank jobs inside each city, or show a row next to its group total. GROUP BY cannot do this because it collapses the rows.
Keep your place in this course
A free account saves which lessons you have finished and unlocks the certificate. The lesson itself stays free to read.
Create free account or sign inCourse complete
You finished every lesson.
Your certificate is ready, and the projects you built are the proof to attach to an application.
Almost there
15 lessons left of 15
Finish every lesson to unlock the certificate. Each one ends in a practice task — do them and the final project brief writes itself.