ROW_NUMBER vs RANK vs DENSE_RANK

All three number rows by an order; they differ only in how they handle ties. The syntax is identical across dialects.

SELECT
  name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,    -- 1, 2, 3, 4   (no ties, arbitrary tiebreak)
  RANK()       OVER (ORDER BY score DESC) AS rnk,         -- 1, 2, 2, 4   (ties share, then a gap)
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk    -- 1, 2, 2, 3   (ties share, no gap)
FROM players;

For two players tied at 2nd place:

function	result	use it when
`ROW_NUMBER`	1, 2, 3, 4	you need a unique number per row (pagination, dedup, top-N)
`RANK`	1, 2, 2, 4	leaderboard where ties share a place and the next is “4th”
`DENSE_RANK`	1, 2, 2, 3	”top 3 distinct scores” — no gaps after ties

Rank within groups by adding PARTITION BY:

SELECT
  team, name, score,
  DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS rank_in_team
FROM players;

“Top 3 distinct scores”. Use DENSE_RANK() ... <= 3 (filtered via QUALIFY or a subquery): it keeps every row whose score is in the top three values, ties included — unlike ROW_NUMBER, which stops at three rows.

Works in MySQL 8+, PostgreSQL, SQLite 3.25+, BigQuery, Snowflake and DuckDB.