# SQL RANK Function: Syntax, PARTITION BY and Examples

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

```sql
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.

1. The `FROM` clause produces the rows.
2. `PARTITION BY` splits those rows into independent groups [1].
3. `ORDER BY` sorts the rows inside each group [1].
4. `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.

```sql
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].

```sql
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.

```sql
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.

```sql
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.

```sql
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.

## References

1. [RANK (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver17)
2. [RANK](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/RANK.html)
3. [dense_rank - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/en-us/azure/databricks/pyspark/reference/functions/dense_rank)
4. [Ranking Functions (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/ranking-functions-transact-sql?view=sql-server-ver17)

## Further Reading

- [Codd EF (1970). A relational model of data for large shared data banks. Communications of the ACM](https://doi.org/10.1145/362384.362685)
- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)
- [SQLite: SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)

## Related Articles

- [SQL Window Functions: PARTITION BY, LEAD, and PERCENT_RANK](/blog/data-analysis/sql-window-functions-partition-by-lead)
- [SQL MAX Function: Syntax and Examples](/blog/data-analysis/sql-max-function-syntax-examples)
- [SQL LEAD Function: Syntax and Examples](/blog/data-analysis/sql-lead-function)
- [SQL COUNT Function: Syntax, Examples and GROUP BY](/blog/data-analysis/sql-count-function)
- [SQL PARTITION BY: Syntax, Examples and When to Use It](/blog/data-analysis/sql-partition-by-clause)