SQL RANK vs DENSE_RANK vs ROW_NUMBER: When Rankings Skip or Repeat

You run a query to rank your top customers by revenue, and halfway down the result set you notice the ranking jumps from 3 to 5. Or two rows share the same rank and you're not sure if that's correct. These aren't bugs — they're the expected behavior of three different SQL window functions that most people use interchangeably until something looks wrong.

Understanding when each function skips, repeats, or stays sequential saves you from bad reports and embarrassing dashboards.

What You'll Learn

• The precise behavior of RANK(), DENSE_RANK(), and ROW_NUMBER()
• Why RANK() skips numbers and when that matters
• How to choose the right function for leaderboards, deduplication, and pagination
• Common mistakes that silently produce wrong results
• A quick reference table to keep close during query writing

Prerequisites

You should be comfortable writing basic SELECT statements and understand what ORDER BY does. Familiarity with the concept of a window function is helpful but not required — the examples below build up from scratch. These functions work in PostgreSQL, MySQL 8+, SQL Server, SQLite 3.25+, and BigQuery.

The Setup: A Sample Table

All examples in this article use a simple sales table. Here's the data we're working with:

CREATE TABLE sales (
  rep_name  TEXT,
  region    TEXT,
  revenue   INTEGER
);

INSERT INTO sales VALUES
  ('Alice',   'East',  9000),
  ('Bob',     'East',  7500),
  ('Carol',   'East',  7500),
  ('Diana',   'East',  6200),
  ('Evan',    'West',  8800),
  ('Fiona',   'West',  8800),
  ('George',  'West',  5100);

Notice that Bob and Carol share the same revenue, and so do Evan and Fiona. Ties are what make these three functions behave differently.

ROW_NUMBER: Sequential, No Ties Allowed

ROW_NUMBER() assigns a unique integer to every row, starting at 1, regardless of whether values are equal. Two rows with identical values still get different numbers — whichever row the database processes first gets the lower number.

SELECT
  rep_name,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num
FROM sales;

Result:

Evan and Fiona both earned 8800, but one of them gets row 2 and the other gets row 3. The choice is arbitrary — the database doesn't guarantee which one comes first unless you add a tiebreaker column to your ORDER BY. If you need deterministic output, always include a unique column as a secondary sort key.

Best for: pagination (WHERE row_num BETWEEN 11 AND 20), deduplication (keep only the first row per group), and any situation where you need exactly one row per rank slot.

RANK: Honest About Ties, Skips Afterward

RANK() gives tied rows the same rank, then skips ahead by the number of tied rows before continuing. Think of it like a race podium where two athletes share second place and nobody gets third.

SELECT
  rep_name,
  revenue,
  RANK() OVER (ORDER BY revenue DESC) AS rnk
FROM sales;

Result:

Evan and Fiona both rank 2. The next rank jumps to 4, not 3, because two rows occupied positions 2 and 3. Bob and Carol both rank 4, and then Diana jumps to rank 6. This is where the