Lesson 4 of 14 · 24 min
RANK and DENSE_RANK: rankings with ties
By the end of this lesson you can rank rows with RANK and DENSE_RANK, predict the numbers each one gives for tied values, and pick the right function for questions like "top earners" or "the third-highest salary".
ROW_NUMBER gives every row its own number, so two employees on exactly the same salary get different positions, decided by a tie-breaker. That's right for picking one row. It's wrong for a league table, where equal values deserve equal positions. SQL has two more ranking functions for that:
RANK()gives tied rows the same rank and then skips numbers: 1, 2, 2, 4. It's the sports-table convention: if two people share second place, nobody is third.DENSE_RANK()gives tied rows the same rank and does not skip: 1, 2, 2, 3. Each distinct value gets the next number, so DENSE_RANK counts distinct values.
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