Ranking and top N per group

In a live SQL round, "top N per group" is the window-function question you are most likely to get, and the follow-up is nearly always about ties: "what if two of them are equal?" This lesson shows how to choose between ROW_NUMBER, RANK and DENSE_RANK on purpose, return the top N rows inside each group, read "the Nth value" correctly when values repeat, pull the latest row per key, and make each answer come out the same on every run.

Three ways to number tied rows

A window function computes a value for each row from a set of related rows, its window, without collapsing those rows into one the way GROUP BY does. Three window functions number rows in the order you give them:

• ROW_NUMBER() numbers 1, 2, 3, 4 with no repeats, even when rows tie.

• RANK() gives tied rows the same number, then skips: 1, 2, 2, 4.

• DENSE_RANK() gives tied rows the same number and does not skip: 1, 2, 2, 3.

The examples use a running club's Saturday 5K. The table results has one row per runner per Saturday, with the finish time in seconds (lower is better):

Here is the 7 March run, numbered all three ways. With no PARTITION BY, the window is every row left after WHERE, here the four runners of 7 March.