The ranking family includes ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, and CUME_DIST. ROW_NUMBER assigns a unique sequential number to each row within a partition, making it the go-to choice when each row must receive a distinct position. RANK and DENSE_RANK both assign the same rank to tied values, but RANK leaves gaps afterward (so a tie at rank 1 followed by another tie produces ranks 1, 1, 3), while DENSE_RANK continues consecutively (1, 1, 2). Choosing between them hinges on whether gaps in ranking should propagate: DENSE_RANK is preferred for top-N lists where consecutive ranks carry meaning, while RANK is preferred when gaps communicate the existence of ties.
NTILE(N) distributes rows into N buckets as evenly as possible, returning bucket numbers from 1 to N; when the row count is not perfectly divisible, the first (rows mod N) buckets receive an extra row. PERCENT_RANK computes (rank - 1) / (rows - 1), giving a relative position between 0 and 1, while CUME_DIST returns the fraction of rows with values less than or equal to the current row's value, ranging from 0 to 1 inclusive. These two functions complement NTILE for percentile-based bucketing and serve slightly different analytical purposes, with PERCENT_RANK emphasizing relative position and CUME_DIST emphasizing cumulative mass.
The classic "top N per group" pattern relies on ROW_NUMBER inside a subquery, with rn <= N in the outer filter. When ties should be preserved across the boundary, RANK or DENSE_RANK should be used with a rank <= N filter instead, which includes all tied items rather than arbitrarily cutting them off. A subtle but critical detail is that the ORDER BY in the ranking window must include a tiebreaker such as a unique id or timestamp; without it, equal-keyed rows can shuffle between runs, producing non-deterministic results and unstable pagination. Pagination built on ROW_NUMBER inherits the same instability and is best replaced with keyset pagination using WHERE id > last_seen for large datasets, since OFFSET scans and discards skipped rows.