Lesson 3 of 14 · 24 min
ROW_NUMBER: numbering rows within groups
By the end of this lesson you can number rows within groups with ROW_NUMBER, pick the first or latest row per group, make the numbering deterministic with tie-breakers, and use it to flag duplicates.
ROW_NUMBER() is the first of the ranking functions. It doesn't aggregate anything; it simply numbers the rows of each partition 1, 2, 3 … in the order given by the ORDER BY inside OVER. Unlike SUM or AVG, a ranking function requires an ORDER BY in its window, because a number only means something if you know what it counts in.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons