How to Assign Rank in SQL: A Complete Guide to Ranking Functions

Ranking rows in SQL is one of the most practical skills for anyone working with relational databases. Whether you're building leaderboards, paginating results, or isolating the top performer per category, SQL provides dedicated window functions designed exactly for this purpose.

What Does "Ranking" Mean in SQL?

In SQL, ranking assigns a sequential or scored position to each row within a result set — based on the value of one or more columns. Unlike simple ORDER BY, ranking functions attach a numeric rank to each row without collapsing the dataset. You get all your rows back, each labeled with its position.

This is made possible through window functions — a class of SQL functions that operate across a defined "window" of rows related to the current row.

The Three Core SQL Ranking Functions

SQL offers three standard ranking functions, each behaving slightly differently when it encounters ties (duplicate values in the ranked column).

Function	Ties Handled How	Gaps in Sequence?
`RANK()`	Same rank for ties	Yes — skips numbers
`DENSE_RANK()`	Same rank for ties	No — no gaps
`ROW_NUMBER()`	Unique rank for every row	No ties allowed

RANK()

RANK() assigns the same number to tied rows, then skips ahead. If two rows tie for position 2, the next row gets rank 4 — position 3 is skipped.

SELECT employee_name, sales_total, RANK() OVER (ORDER BY sales_total DESC) AS sales_rank FROM employees;

DENSE_RANK()

DENSE_RANK() also assigns the same number to ties, but it does not skip. After two rows tied at rank 2, the next row gets rank 3.

SELECT employee_name, sales_total, DENSE_RANK() OVER (ORDER BY sales_total DESC) AS sales_rank FROM employees;

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential integer to every row regardless of ties. When values are identical, the order between tied rows is technically arbitrary unless you add a tiebreaker column to your ORDER BY.

SELECT employee_name, sales_total, ROW_NUMBER() OVER (ORDER BY sales_total DESC) AS row_num FROM employees;

The OVER() Clause: The Engine Behind Ranking 🔧

All three functions require an OVER() clause. This is what defines the window — the set of rows each function looks at when computing a rank.

At minimum, OVER() contains an ORDER BY that determines ranking direction. You can also add PARTITION BY to rank within subgroups rather than across the entire table.

Ranking Within Groups Using PARTITION BY

PARTITION BY is where ranking gets genuinely powerful. It resets the rank counter for each group — like running a separate ranking query for every category simultaneously.

SELECT department, employee_name, sales_total, RANK() OVER ( PARTITION BY department ORDER BY sales_total DESC ) AS dept_rank FROM employees;

This returns every employee with their rank within their own department, not against the whole company. Without PARTITION BY, you'd need multiple queries or complex subqueries to achieve the same result.

Practical Use Cases for SQL Ranking

Top-N per group: Pull the top 3 salespeople from each region, or the most recent 5 orders per customer.

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY region ORDER BY sales_total DESC ) AS rn FROM sales ) ranked WHERE rn <= 3;

Percentile and competitive scoring: Use RANK() or DENSE_RANK() for scoring tables where ties should share a position.

Deduplication:ROW_NUMBER() is frequently used to identify and remove duplicate rows — assign a row number per duplicate group, then delete all rows where rn > 1.

Variables That Affect Which Function You Should Use

Choosing the right ranking function depends on several factors specific to your data and goal:

Whether ties exist in your data — if your ranked column has many duplicate values, RANK() and DENSE_RANK() behave very differently from ROW_NUMBER()
Whether gaps in rank sequence matter — in competitive rankings, a gap between 2nd and 4th place (skipping 3rd) may confuse end users
Whether you need deterministic output — ROW_NUMBER() requires a reliable tiebreaker to produce consistent results across query executions
Your SQL dialect — RANK(), DENSE_RANK(), and ROW_NUMBER() are ANSI SQL standard and widely supported (PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite 3.25+), but older MySQL versions lack window function support entirely
Query performance — on large datasets, window functions with poorly indexed ORDER BY columns can be slow; partitioning strategy matters

🧩 A Note on Compatibility

If you're working with MySQL versions below 8.0, window functions are not available. Common workarounds include user-defined variables or correlated subqueries — both more verbose and less readable than modern window functions. Upgrading to MySQL 8+ or switching to a compatible database engine unlocks the full ranking toolkit.

How Your Use Case Changes Everything

The gap between knowing these functions and applying them correctly lies in your specific data structure and output requirements. A dataset with frequent ties produces dramatically different results depending on whether you reach for RANK(), DENSE_RANK(), or ROW_NUMBER(). The right partitioning strategy depends entirely on how your data is organized — whether rankings should reset by region, date, category, user, or something else.

How those ranked results will be displayed or processed downstream also shapes which approach fits — a front-end leaderboard, a backend deduplication job, and a paginated API response each have different tolerance for gaps, ties, and non-determinism. The functions are straightforward; the decision about which fits your situation is where the real work lives.