Ranking Functions

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:

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

┌────────┬───────┬─────────┬──────┬────────────┐
│  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”:

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

┌────────┬───────┬──────┬──────────────┬───────────┐
│  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:

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

┌──────────┬───────┬───────┬──────┬─────────┐
│  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:

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

┌───────────┬────────┬─────────┬────────────┐
│ 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.