---
title: 'Ranking Functions'
source: 'https://academia.sh/en/courses/advanced-sql/ranking-functions'
course: 'Advanced SQL'
language: en
updated: '2026-08-23T07:00:37+00:00'
license: 'CC BY-SA 4.0'
---

# Ranking Functions

The tie behavior of row number, rank, and dense rank, tile and percentile distribution functions, selecting the top N within a partition, and deduplication with row number.

The previous lesson ran computations over a window but did not give the rows a rank.
Questions like "the three members who borrowed the most" first want a rank number, and
right after that call for a decision: should two members with an equal count get the same
rank, or different ones? This question has three possible answers, and SQL defines all
three as separate functions.

This lesson places the ranking functions side by side on the same data, shows their
differences, and establishes the condition needed for a ranking to be deterministic.

## Three Functions, Three Tie Decisions

All three functions order the window with `ORDER BY` and give every row an integer. Where
they part ways is what happens with rows that are **equal in value** on the sort key:

- `ROW_NUMBER`: does not recognize ties. It gives every row a different number, proceeding
  1, 2, 3.
- `RANK`: gives ties the same rank, then **skips**. If two rows are second, the next one
  is fourth.
- `DENSE_RANK`: gives ties the same rank, **without skipping**. If two rows are second,
  the next one is third.

All three can be written in the same query; the shared window is defined once in a
`WINDOW` clause:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch TEXT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO member VALUES (1,'Alice','Kadikoy'),(2,'Ben','Kadikoy'),(3,'Clara','Uskudar'),
  (4,'Derek','Uskudar'),(5,'Evan','Besiktas'),(6,'Fiona','Besiktas'),
  (7,'Grace','Kadikoy'),(8,'Hannah','Uskudar');
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

WITH count AS (
  SELECT m.name, m.branch, COUNT(l.id) AS count
  FROM member m LEFT JOIN loan l ON l.member_id = m.id
  GROUP BY m.id, m.name, m.branch
)
SELECT name, count,
       ROW_NUMBER() OVER s AS row_num,
       RANK()       OVER s AS rank,
       DENSE_RANK() OVER s AS dense_rank
FROM count
WINDOW s AS (ORDER BY count DESC)
ORDER BY count DESC, name;
SQL
```

```text
┌────────┬───────┬─────────┬──────┬────────────┐
│  name  │ count │ row_num │ rank │ dense_rank │
├────────┼───────┼─────────┼──────┼────────────┤
│ Alice  │ 5     │ 1       │ 1    │ 1          │
│ Ben    │ 4     │ 2       │ 2    │ 2          │
│ Clara  │ 4     │ 3       │ 2    │ 2          │
│ Derek  │ 3     │ 4       │ 4    │ 3          │
│ Evan   │ 3     │ 5       │ 4    │ 3          │
│ Fiona  │ 3     │ 6       │ 4    │ 3          │
│ Grace  │ 2     │ 7       │ 7    │ 4          │
│ Hannah │ 1     │ 8       │ 8    │ 5          │
└────────┴───────┴─────────┴──────┴────────────┘
```

Two members have four loans each. `RANK` gave both of them 2, and gave the next member 4,
not 3: third place was counted as consumed. `DENSE_RANK` gave both of them 2 and gave the
next one 3; the rank numbers advanced with no gap. `ROW_NUMBER` did not see the tie at all
and distributed 2 and 3 arbitrarily.

These three columns correspond to three separate questions. `RANK` is "how many members
got more than me" with one added — the habit of competition rankings. `DENSE_RANK`
answers "how many distinct values are greater than mine" and is suited for counting
levels. `ROW_NUMBER` produces not a rank but an **identity**: it is used to tell rows
apart from each other.

In the last row, `RANK` gives `Hannah` 8, `DENSE_RANK` gives 5. The difference between the
two numbers is the combined effect of the ties: eight members, but five distinct values.

## The Indeterminacy of a Tie

In the `ROW_NUMBER` column, `Ben` got 2 and `Clara` got 3. This distribution does not
follow from how the query is written: since both have a `count` value of 4,
`ORDER BY count DESC` does not separate them. Which number went to which was the engine's
decision.

This is a situation to be avoided. The same query, under a different plan, in a different
version, or with the table read in a different order, can swap the numbers. If
`ROW_NUMBER` is being used to paginate or deduplicate a result set, this swap turns into a
silent bug: a record appears twice across pages, and another one never appears at all.

The rule is clear: in a window where `ROW_NUMBER` is used, the `ORDER BY` key must
determine the rows **uniquely**. If the key does not do this, a unique column, such as a
primary key, is added to the end — the phrasing `ORDER BY count DESC, name` removes the
indeterminacy shown above. `RANK` and `DENSE_RANK` are not affected by this problem,
because they already give tied rows the same value.

## Tile and Percentile Distribution

The other members of the ranking family give a row's **position** in the ordering as a
proportion. `NTILE(n)` splits the partition into `n` parts as evenly as possible;
`PERCENT_RANK` gives the rank's counterpart between zero and one, and `CUME_DIST` gives
"the proportion of rows up to and including this one":

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch TEXT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO member VALUES (1,'Alice','Kadikoy'),(2,'Ben','Kadikoy'),(3,'Clara','Uskudar'),
  (4,'Derek','Uskudar'),(5,'Evan','Besiktas'),(6,'Fiona','Besiktas'),
  (7,'Grace','Kadikoy'),(8,'Hannah','Uskudar');
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

WITH count AS (
  SELECT m.name, COUNT(l.id) AS count
  FROM member m LEFT JOIN loan l ON l.member_id = m.id
  GROUP BY m.id, m.name
)
SELECT name, count,
       NTILE(4) OVER s AS tile,
       ROUND(PERCENT_RANK() OVER s, 2) AS percent_rank,
       ROUND(CUME_DIST()    OVER s, 2) AS cume_dist
FROM count
WINDOW s AS (ORDER BY count DESC)
ORDER BY count DESC, name;
SQL
```

```text
┌────────┬───────┬──────┬──────────────┬───────────┐
│  name  │ count │ tile │ percent_rank │ cume_dist │
├────────┼───────┼──────┼──────────────┼───────────┤
│ Alice  │ 5     │ 1    │ 0.0          │ 0.13      │
│ Ben    │ 4     │ 1    │ 0.14         │ 0.38      │
│ Clara  │ 4     │ 2    │ 0.14         │ 0.38      │
│ Derek  │ 3     │ 2    │ 0.43         │ 0.75      │
│ Evan   │ 3     │ 3    │ 0.43         │ 0.75      │
│ Fiona  │ 3     │ 3    │ 0.43         │ 0.75      │
│ Grace  │ 2     │ 4    │ 0.86         │ 0.88      │
│ Hannah │ 1     │ 4    │ 1.0          │ 1.0       │
└────────┴───────┴──────┴──────────────┴───────────┘
```

The row that stands out is `Ben` and `Clara`: they have the same `count` value, the same
percentile rank, and the same cumulative distribution, but they fell into different
tiles. `NTILE` does not consider ties; it splits rows by number, and if a partition
boundary falls in the middle of a set of peers, it splits them apart. This is why the tile
number is, for rows of equal value, as indeterminate as `ROW_NUMBER`.

`PERCENT_RANK` always gives the first row 0, `CUME_DIST` always gives the last row 1. The
difference between the two is in their definitions: percentile rank measures the
proportion "how many rows are ahead of me," cumulative distribution measures the
proportion "of me and everyone not greater than me."

## Top N Within a Partition

The most common use of ranking functions is selecting the best of each partition. As
noted in the previous lesson, a window function's result cannot be used in the same
query's `WHERE` clause; the ranking is done in a common table expression and the
filtering happens on the outside:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch TEXT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO member VALUES (1,'Alice','Kadikoy'),(2,'Ben','Kadikoy'),(3,'Clara','Uskudar'),
  (4,'Derek','Uskudar'),(5,'Evan','Besiktas'),(6,'Fiona','Besiktas'),
  (7,'Grace','Kadikoy'),(8,'Hannah','Uskudar');
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

WITH count AS (
  SELECT m.name, m.branch, COUNT(l.id) AS count
  FROM member m LEFT JOIN loan l ON l.member_id = m.id
  GROUP BY m.id, m.name, m.branch
),
ranked AS (
  SELECT branch, name, count,
         RANK()       OVER b AS rank,
         ROW_NUMBER() OVER b AS row_num
  FROM count
  WINDOW b AS (PARTITION BY branch ORDER BY count DESC)
)
SELECT branch, name, count, rank, row_num FROM ranked
WHERE rank <= 1 ORDER BY branch, name;
SQL
```

```text
┌──────────┬───────┬───────┬──────┬─────────┐
│  branch  │ name  │ count │ rank │ row_num │
├──────────┼───────┼───────┼──────┼─────────┤
│ Besiktas │ Evan  │ 3     │ 1    │ 1       │
│ Besiktas │ Fiona │ 3     │ 1    │ 2       │
│ Kadikoy  │ Alice │ 5     │ 1    │ 1       │
│ Uskudar  │ Clara │ 4     │ 1    │ 1       │
└──────────┴───────┴───────┴──────┴─────────┘
```

Besiktas gave two rows, the others gave one each. The cause is the choice of function: with
`RANK`, filtering keeps all the tied rows; with `WHERE row_num <= 1`, `Fiona` would have
dropped out, and which one dropped would have been indeterminate.

The choice depends on what the question wants. If "each branch's top-borrowing member"
wants two names on a tie, `RANK` is right; if it wants exactly one row, `ROW_NUMBER` is
right — and in the second case an additional tie-breaking column also has to be specified.
Changing the threshold in `WHERE rank <= 3` extends the same query to the top three.

## Deduplication with Row Number

`ROW_NUMBER` is also used as a deduplication tool, not just a ranking one. Taking only the
first of the numbered rows within a partition is the "one representative per group"
pattern:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch TEXT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO member VALUES (1,'Alice','Kadikoy'),(2,'Ben','Kadikoy'),(3,'Clara','Uskudar'),
  (4,'Derek','Uskudar'),(5,'Evan','Besiktas'),(6,'Fiona','Besiktas'),
  (7,'Grace','Kadikoy'),(8,'Hannah','Uskudar');
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

WITH numbered AS (
  SELECT id, member_id, pickup,
         ROW_NUMBER() OVER (PARTITION BY member_id ORDER BY pickup DESC, id DESC) AS n
  FROM loan
)
SELECT n.member_id, m.name, n.id AS loan_id, n.pickup
FROM numbered n JOIN member m ON m.id = n.member_id
WHERE n.n = 1 ORDER BY n.member_id;
SQL
```

```text
┌───────────┬────────┬─────────┬────────────┐
│ member_id │  name  │ loan_id │   pickup   │
├───────────┼────────┼─────────┼────────────┤
│ 1         │ Alice  │ 5       │ 2024-03-18 │
│ 2         │ Ben    │ 9       │ 2024-03-21 │
│ 3         │ Clara  │ 13      │ 2024-03-25 │
│ 4         │ Derek  │ 16      │ 2024-03-20 │
│ 5         │ Evan   │ 19      │ 2024-03-25 │
│ 6         │ Fiona  │ 22      │ 2024-03-20 │
│ 7         │ Grace  │ 24      │ 2024-03-18 │
│ 8         │ Hannah │ 25      │ 2024-03-13 │
└───────────┴────────┴─────────┴────────────┘
```

The sort key `pickup DESC, id DESC` has two columns. `pickup` alone can repeat within the
same member; adding `id`, which is a primary key, makes the result deterministic. This is
the previous section's rule put into practice.

The same result could have been produced with the `MAX` subquery from the Correlated
Subqueries lesson. The difference between the two phrasings shows up on a tie: the
subquery-based phrasing returns both records that share the same date, the row-number
phrasing selects exactly one.

## Summary

- `ROW_NUMBER` does not recognize ties, `RANK` gives ties the same rank and skips, and
  `DENSE_RANK` gives ties the same rank without skipping.
- `RANK` answers "how many rows are ahead of me," `DENSE_RANK` answers "how many distinct
  values are ahead of me"; `ROW_NUMBER` produces not a rank but an identity.
- `ROW_NUMBER` and `NTILE` results are not deterministic if the sort key does not
  determine the rows uniquely; a unique column has to be added to the key.
- Filtering on a window result requires wrapping the query in a common table expression;
  in a top-N-per-partition selection, `RANK` returns all the tied rows and `ROW_NUMBER`
  returns exactly N.
- `PERCENT_RANK` gives the first row 0, `CUME_DIST` gives the last row 1; `NTILE` can
  split peer rows into different tiles.

## Next Step

Every query written up to this point produced a result whose columns were known at the
time the query was written. In reports, though, the opposite is often wanted: values
sitting in rows should move up to a **column header**, so that each genre or each month
becomes a separate column. This transformation reduces the row count and raises the
column count, and it runs directly against SQL's rule of a fixed column list. The next
lesson shows how pivot operations are written with conditional aggregation and how the
reverse transformation, from column to row, is done.