SQL RANK Function: Syntax, PARTITION BY and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The SQL RANK function returns the position of each row inside a group, where rows with equal sort values share the same rank. It is a window function, so it needs an OVER clause with an ORDER BY, and it can be split into groups with PARTITION BY. The rank of a row is one plus the number of ranks that come before it [1].

Quick Answer

RANK() is a window function. It returns the rank of each row within a partition of a result set [1].
The ORDER BY inside OVER is required. The PARTITION BY is optional, and without it the whole result set is treated as one group [1].
Tied rows get the same rank, and the next rank is skipped. A sequence can look like 1, 2, 2, 4, 5 [1].
RANK is calculated when the query runs. It is a temporary value, not stored in the table [1].
Use it for top-N and bottom-N reporting, such as the highest revenue per region [2].

Syntax

RANK() OVER ( [ partition_by_clause ] order_by_clause )

Argument	Required?	Meaning
`RANK()`	Yes	The function itself. It takes no arguments.
`PARTITION BY`	No	Divides the result set into partitions the function is applied to. If omitted, all rows form a single group [1].
`ORDER BY`	Yes	Determines the order of the data before the function is applied [1].

The <rows or range clause> of the OVER clause cannot be specified for RANK [1]. That means you cannot write ROWS BETWEEN ... with this function.

How It Works

The database evaluates the window in a fixed order.

The FROM clause produces the rows.
PARTITION BY splits those rows into independent groups [1].
ORDER BY sorts the rows inside each group [1].
RANK() assigns a number to each row.

The number follows one rule. A row's rank is one plus the number of ranks that come before it [1]. When two rows tie, both receive the same rank, and the following rank jumps ahead by the number of tied rows [2].

For a group with revenues 15000, 15000, 12000 and 9000, the ranks are 1, 1, 3 and 4. Two rows share rank 1, so rank 2 never appears.

The formula for the next rank after a tie is:

$$\text{next rank} = \text{tied rank} + \text{number of tied rows}$$

That is why RANK does not always return consecutive integers [1]. If you need consecutive numbers with no gaps, use DENSE_RANK instead. The difference is that DENSE_RANK leaves no gaps in the ranking sequence when there are ties [3].

Worked Example

The dataset is a sales table with one row per salesperson, holding a region and a revenue figure.

sale_id	region	salesperson	revenue
1	North	Alice	12000
2	North	Bob	15000
3	North	Carol	15000
4	North	Dave	9000
5	South	Eve	18000
6	South	Frank	11000
7	South	Grace	18000
8	East	Heidi	7000
9	East	Ivan	13000
10	East	Judy	13000
11	West	Mallory	16000
12	West	Niaj	14000

The query ranks each salesperson inside their own region by revenue, highest first.

SELECT
  region,
  salesperson,
  revenue,
  RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS revenue_rank
FROM sales
ORDER BY region, revenue_rank;

The result was checked with an equivalent SQLite query.

region	salesperson	revenue	revenue_rank
East	Ivan	13000	1
East	Judy	13000	1
East	Heidi	7000	3
North	Bob	15000	1
North	Carol	15000	1
North	Alice	12000	3
North	Dave	9000	4
South	Eve	18000	1
South	Grace	18000	1
South	Frank	11000	3
West	Mallory	16000	1
West	Niaj	14000	2

Read the East rows first. Ivan and Judy both earned 13000, so both rank 1. Heidi earned 7000, and because two rows rank above her, she lands at rank 3. Rank 2 is skipped.

North shows the same pattern with a longer tail. Bob and Carol tie at 15000 and share rank 1. Alice takes rank 3 and Dave takes rank 4. The gap appears only where a tie exists.

South has two rows at 18000, so it ties, and Frank drops to rank 3. West has two distinct revenues, so the ranks run 1 and 2 with no gap.

The PARTITION BY region clause is what keeps these four groups separate. Without it, all twelve rows would be ranked together and the regions would be mixed. If you want to see how partitioning behaves in other window functions, the guide to SQL PARTITION BY covers the clause on its own.

More Examples

Rank the whole table with no partition. Drop PARTITION BY and the entire result set becomes one group [1].

SELECT
  salesperson,
  revenue,
  RANK() OVER (ORDER BY revenue DESC) AS overall_rank
FROM sales
ORDER BY overall_rank;

Eve and Grace both earned 18000, so both take rank 1. Mallory at 16000 takes rank 3, because two rows rank above her.

Filter to the top N per group. Wrap the ranked query in a subquery and keep only the rows you want.

SELECT region, salesperson, revenue, revenue_rank
FROM (
  SELECT
    region,
    salesperson,
    revenue,
    RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS revenue_rank
  FROM sales
) ranked
WHERE revenue_rank = 1
ORDER BY region;

This returns Bob, Carol, Eve, Grace, Ivan, Judy and Mallory. Every tied leader is kept, which is the behavior you want when a tie is a real tie. If you need exactly one row per group, use SQL ROW_NUMBER instead, since it numbers all rows sequentially with no repeats [1].

Rank ascending. Change DESC to ASC to rank the lowest values first. The tie behavior does not change.

SELECT
  region,
  salesperson,
  revenue,
  RANK() OVER (PARTITION BY region ORDER BY revenue ASC) AS low_rank
FROM sales
ORDER BY region, low_rank;

Heidi now ranks 1 in East, Dave ranks 1 in North, Frank ranks 1 in South and Niaj ranks 1 in West.

Compare RANK with DENSE_RANK. Running both side by side shows the gap clearly.

SELECT
  region,
  salesperson,
  revenue,
  RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS dense_rnk
FROM sales
ORDER BY region, rnk;

In East, Ivan and Judy get 1 from both functions. Heidi gets 3 from RANK and 2 from DENSE_RANK. The SQL window functions guide walks through how these ranking functions relate to each other.

Errors and How to Fix Them

Missing ORDER BY. In SQL Server, RANK() OVER () fails because the ORDER BY clause is required [1]. PostgreSQL, MySQL and SQLite accept it but give every row rank 1. Add an ordering column inside the OVER clause.

Adding a frame clause. RANK() OVER (ORDER BY revenue ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is not allowed. The <rows or range clause> cannot be specified for RANK [1]. Remove the frame.

Using RANK in WHERE. You cannot filter on a window function in the same query that defines it, because the window is evaluated after WHERE. Put the ranked query in a subquery or a CTE and filter the outer query.

Expecting consecutive numbers. If your output shows 1, 1, 3, that is correct behavior, not a bug. Switch to DENSE_RANK if you need 1, 1, 2 [3].

Mixing partitions in the output. If ranks repeat across regions and you expected one global ranking, you left PARTITION BY in the query. Remove it to rank the whole result set as one group [1].

Common Mistakes

Assuming RANK always returns 1, 2, 3, 4. It skips numbers after ties. The fix is to accept the gap or use DENSE_RANK when consecutive values matter [3].
Forgetting that PARTITION BY resets the counter. Each partition starts at rank 1 again. If you want one continuous ranking, omit PARTITION BY [1].
Using RANK to pick a single winner from a tie. Ties produce multiple rows with rank 1. Use ROW_NUMBER if you need exactly one row per group [1].
Sorting the outer query by the rank column without a partition column. Ranks from different partitions interleave. Add the partition column to the outer ORDER BY, as the worked example does.
Treating the rank as stored data. RANK is a temporary value calculated when the query runs [1]. It changes whenever the underlying data or the ordering changes.
Confusing RANK with an aggregate. RANK is a ranking function, and ranking functions are nondeterministic [4]. It does not collapse rows the way SUM or COUNT does.

Limitations

RANK cannot tell you why two rows tied. It reports the shared position and nothing about the underlying values, so a tie between two identical revenues and a tie between two revenues that differ by a cent look the same in the output. Check the raw values before you treat a tie as meaningful.

The function also cannot break ties on its own. If you need a deterministic single ordering, add a second sort column inside ORDER BY, such as a unique ID. Without that, the database is free to return tied rows in any order, and ranking functions are nondeterministic by definition [4]. Finally, RANK cannot be used with a frame clause, so it will not give you running or sliding calculations [1].

Frequently Asked Questions

What is the difference between RANK and DENSE_RANK?

RANK skips numbers after a tie, so a sequence can read 1, 2, 2, 4. DENSE_RANK leaves no gaps, so the same data reads 1, 2, 2, 3 [3]. Both give tied rows the same value. Pick RANK when the gap carries meaning, such as a competition standing, and DENSE_RANK when you want compact tiers.

Can I use RANK without PARTITION BY?

Yes. The PARTITION BY clause is optional. If you do not specify it, the function treats all rows of the query result set as a single group [1]. You still must include an ORDER BY inside the OVER clause.

Why does my rank jump from 1 to 3?

Because two rows tied for rank 1. A row's rank is one plus the number of ranks that come before it, so after two tied rows the next rank is 3 [1]. Oracle describes the same rule as adding the number of tied rows to the tied rank [2]. This is expected behavior.

Does RANK store a value in the table?

No. RANK is a temporary value calculated when the query is run [1]. To persist numbers in a table, you would use an identity property or a sequence instead [1]. The rank is recomputed on every execution.

Which databases support the RANK function?

RANK is part of standard SQL and appears across major systems. Microsoft documents it for Transact-SQL and Fabric [1], Oracle documents it in its SQL reference [2], and Databricks documents the related DENSE_RANK for Spark SQL [3]. Syntax is consistent, so the examples here transfer with minor dialect changes.