RANK vs DENSE_RANK vs ROW_NUMBER in SQL: Complete Comparison

By Sharon Ben-Moshe · October 1, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Compare RANK, DENSE_RANK, and ROW_NUMBER in SQL. See visual examples of how ties and numbering gaps behave across PostgreSQL, MySQL, and SQL Server.

Quick answer

The difference between ROW_NUMBER, RANK, and DENSE_RANK lies in how they handle ties. ROW_NUMBER always assigns unique sequential integers (1, 2, 3, 4) regardless of ties. RANK assigns identical numbers to ties and skips subsequent numbers (1, 2, 2, 4). DENSE_RANK assigns identical numbers to ties without skipping subsequent numbers (1, 2, 2, 3).

FAQs

When should I use ROW_NUMBER instead of RANK?

Use ROW_NUMBER when you need an absolute unique identifier per row for pagination, deduplication (picking arbitrary top 1), or when ties must still be sorted into distinct slots.

When should I use DENSE_RANK?

Use DENSE_RANK for competition-style rankings where you want to know the top N distinct tiers (e.g., top 3 highest salary bands) without skipping positions after ties.

What happens if ORDER BY is omitted in these window functions?

Most SQL engines require an ORDER BY clause inside the OVER() clause for ROW_NUMBER, RANK, and DENSE_RANK. Without ORDER BY, ranking is undefined and throws a syntax error.

Are these functions supported in SQLite and MySQL?

Yes. SQLite added window functions in version 3.25.0, and MySQL added them in version 8.0. PostgreSQL, SQL Server, and BigQuery have supported them for years.

SQL Dialects

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

By Sharon Ben-Moshe · Oct 13, 2026

The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.

Read post

SQL Dialects

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

By Sharon Ben-Moshe · Oct 13, 2026

The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.

Read post

SQL Dialects

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

ROW_NUMBER() always increments continuously (1, 2, 3, 4, 5) with zero gaps and no duplicate ranks.

RANK() produces ties with gaps (1, 2, 2, 4, 5) mirroring traditional sports leaderboards.

DENSE_RANK() produces ties without gaps (1, 2, 2, 3, 4) representing consecutive tier levels.

All three functions require an ORDER BY clause inside OVER() across all major SQL dialects.

Visualizing ties: Side-by-side comparison

Suppose four candidates take an assessment and two score an identical 95 points. Here is how each ranking function calculates their position:

Demonstrating ROW_NUMBER, RANK, and DENSE_RANK on tied scores

SELECT
  student_name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank_num,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num
FROM exam_results;

-- Result set:
-- student_name | score | row_num | rank_num | dense_rank_num
-- -------------+-------+---------+----------+---------------
-- Alice        |   100 |       1 |        1 |              1
-- Bob          |    95 |       2 |        2 |              2
-- Charlie      |    95 |       3 |        2 |              2
-- Diana        |    90 |       4 |        4 |              3

Selecting the top N distinct values

A common business question is: 'Find all employees in the top 3 highest salaries per department.' If multiple employees share the top salary, ROW_NUMBER drops tied candidates while RANK skips tiers. DENSE_RANK guarantees all top 3 tiers are returned:

Finding the top 3 salary tiers per department

WITH ranked_salaries AS (
  SELECT
    employee_id,
    department_id,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS salary_tier
  FROM employees
)
SELECT employee_id, department_id, salary, salary_tier
FROM ranked_salaries
WHERE salary_tier <= 3;

Common mistakes to avoid

Using ROW_NUMBER() for deduplication without specifying secondary deterministic tie-breaking columns in ORDER BY.

Filtering on the window function alias directly in the WHERE clause instead of using a CTE or subquery (or BigQuery QUALIFY).

Assuming RANK() returns consecutive numbers; any tie will create a gap in subsequent rank values.

RANK vs DENSE_RANK vs ROW_NUMBER in SQL: Complete Comparison

Quick answer

FAQs

When should I use ROW_NUMBER instead of RANK?

When should I use DENSE_RANK?

What happens if ORDER BY is omitted in these window functions?

Are these functions supported in SQLite and MySQL?

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Visualizing ties: Side-by-side comparison

Selecting the top N distinct values

Common mistakes to avoid

Official references

Quick answer

FAQs

When should I use ROW_NUMBER instead of RANK?

When should I use DENSE_RANK?

What happens if ORDER BY is omitted in these window functions?

Are these functions supported in SQLite and MySQL?

Keep reading

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Key takeaways

Visualizing ties: Side-by-side comparison

Selecting the top N distinct values

Common mistakes to avoid

Related SQL guides

Official references