Ranking and Ties

ROW_NUMBER gave 102 the lower number, but the MySQL manual says only that ROW_NUMBER "assigns peers different row numbers." Nothing in the query asks for 102 to come first, so the choice between the two peers is not part of the result's definition. The manual is more direct about a window with no ORDER BY at all: "Without ORDER BY, row numbering is nondeterministic." In both cases, the query leaves that choice to the engine, so a result that must not change from run to run needs a fuller ORDER BY.

Top N per Group

A "top 3 per show" query computes a ranking in a CTE and filters it in the outer query, because WHERE cannot read a window result, as Window Functions showed. The function inside the CTE decides which question the query answers. Here DENSE_RANK keeps every episode with one of the show's three highest play counts:

WITH ranked AS (
  SELECT show_name, episode_id, plays,
         DENSE_RANK() OVER (PARTITION BY show_name ORDER BY plays DESC) AS level
  FROM episodes
)
SELECT show_name, level, episode_id, plays
FROM ranked
WHERE level <= 3
ORDER BY show_name, level, episode_id;
Copy

Output:

+-------------+-------+------------+-------+
| show_name   | level | episode_id | plays |
+-------------+-------+------------+-------+
| Byte Size   |     1 |        201 |  6100 |
| Byte Size   |     1 |        203 |  6100 |
| Byte Size   |     2 |        202 |  4400 |
| Byte Size   |     3 |        204 |  3000 |
| Field Notes |     1 |        101 |  5100 |
| Field Notes |     2 |        103 |  4700 |
| Field Notes |     3 |        102 |  3800 |
| Field Notes |     3 |        105 |  3800 |
+-------------+-------+------------+-------+
Copy

Byte Size now returns four episodes, because its three highest play counts are 6100, 4400, and 3000, and two episodes share 6100. The same filter, <= 3, returns a different number of rows with each function.

• ROW_NUMBER: an exact row count. It is the right choice when the result has a fixed number of slots, like a home screen. The cost is that it drops one of two tied rows without any sign, so it needs a tiebreaker to make that choice deliberate.
• RANK: fair to ties, but the count can grow. A tie at the cut adds rows, and a tie above the cut leaves a gap, which is why Byte Size has no place 2. It suits a results list where tied entries share a place.
• DENSE_RANK: counts distinct values. It answers questions about values rather than rows, like "the second highest play count of each show". The cost is that the number of rows has no upper limit when many rows tie.

Tiebreaker Columns

A tiebreaker is an extra ORDER BY column inside the window that decides the order of rows that tie on the main column. For the home screen, the app shows the newer episode first, because it reached the same play count in fewer days. Two episodes could still share a release day, so episode_id comes last and makes every row's position unique:

WITH ranked AS (
  SELECT show_name, episode_id, day_no, plays,
         ROW_NUMBER() OVER (
           PARTITION BY show_name
           ORDER BY plays DESC, day_no DESC, episode_id
         ) AS place
  FROM episodes
)
SELECT show_name, place, episode_id, day_no, plays
FROM ranked
WHERE place <= 3
ORDER BY show_name, place;
Copy

Output:

+-------------+-------+------------+--------+-------+
| show_name   | place | episode_id | day_no | plays |
+-------------+-------+------------+--------+-------+
| Byte Size   |     1 |        203 |     16 |  6100 |
| Byte Size   |     2 |        201 |      2 |  6100 |
| Byte Size   |     3 |        202 |      9 |  4400 |
| Field Notes |     1 |        101 |      4 |  5100 |
| Field Notes |     2 |        103 |     18 |  4700 |
| Field Notes |     3 |        105 |     32 |  3800 |
+-------------+-------+------------+--------+-------+
Copy

Episode 105, released on day 32, now takes third place from episode 102, released on day 11. Each show returns exactly three rows, and the answer no longer depends on which peer the engine happened to number first.

A tiebreaker is a business rule, so it must be stated, not guessed. Once the ORDER BY columns are unique, no rows are peers, and RANK and DENSE_RANK return the same numbers as ROW_NUMBER.

Equal Groups With NTILE

NTILE(n) divides each partition into n groups, in ORDER BY sequence, and returns the group number of each row. The PostgreSQL manual says it divides the partition "as equally as possible", so it splits rows into halves, quartiles, or deciles. The window ends with episode_id, so the split is the same on every run:

SELECT show_name, episode_id, plays,
       NTILE(2) OVER (PARTITION BY show_name ORDER BY plays DESC, episode_id) AS half
FROM episodes
ORDER BY show_name, plays DESC, episode_id;
Copy

Output:

+-------------+------------+-------+------+
| show_name   | episode_id | plays | half |
+-------------+------------+-------+------+
| Byte Size   |        201 |  6100 |    1 |
| Byte Size   |        203 |  6100 |    1 |
| Byte Size   |        202 |  4400 |    2 |
| Byte Size   |        204 |  3000 |    2 |
| Field Notes |        101 |  5100 |    1 |
| Field Notes |        103 |  4700 |    1 |
| Field Notes |        102 |  3800 |    1 |
| Field Notes |        105 |  3800 |    2 |
| Field Notes |        104 |  2900 |    2 |
| Field Notes |        106 |  1200 |    2 |
+-------------+------------+-------+------+
Copy

Field Notes has six episodes, so each half holds three. The peers 102 and 105 are in different halves, because NTILE assigns groups by row position, not by value. NTILE fits when equal group sizes matter more than keeping ties together.

What Ranking Costs

Ranking functions number rows in the window's ORDER BY sequence, so MySQL sorts each partition first. The plan for the three-function query shows one sort shared by all three functions, because they use the same window:

EXPLAIN FORMAT=TREE
SELECT show_name, episode_id,
       ROW_NUMBER() OVER w AS row_num,
       RANK()       OVER w AS rnk,
       DENSE_RANK() OVER w AS dense_rnk
FROM episodes
WINDOW w AS (PARTITION BY show_name ORDER BY plays DESC);
Copy

Output:

-> Window aggregate: row_number() OVER w, rank() OVER w, dense_rank() OVER w  (cost=0 rows=10)
    -> Sort: episodes.show_name, episodes.plays DESC  (cost=1.25 rows=10)
        -> Table scan on episodes  (cost=1.25 rows=10)
Copy

The filter in a top 3 query runs in the outer query, after the window, so the query still reads and sorts every episode even though it keeps only three per show. Ranking functions also ignore any frame clause: the MySQL manual says they "use the entire partition even if a frame is specified."

Practice: Pick Two Featured Episodes

The app now shows two featured episodes per show. For each show, return the two most played episodes. When two episodes have the same plays, the one with the higher day_no comes first, and if day_no is also equal, the lower episode_id comes first. Return show_name, place (1 or 2), episode_id, and plays, sorted by show_name, then place. A show with only one episode returns one row.

Using This in an Interview

Before writing a top N query, ask what a tie should do: "Should two episodes tied for third both appear, or must each show return exactly three?" The answer picks the function, and saying it out loud shows that the tie was considered on purpose. If exactly N rows are required, name the tiebreaker and why it was chosen.

• "Would this query work in PostgreSQL?" Yes. Meta's guide says its SQL screen runs on PostgreSQL and that standard ANSI SQL is enough, and the PostgreSQL manual defines rank and dense_rank with the same gap rules as MySQL.
• "How would you find the second highest play count per show?" Use DENSE_RANK and keep rank 2. RANK can skip 2 after a tie, and in Byte Size, ROW_NUMBER = 2 returns 6100, which is the highest count again.

Key Takeaways

• ROW_NUMBER gives every row a different number, RANK gives peers the same number and leaves a gap after them, and DENSE_RANK gives peers the same number without a gap.
• The same <= 3 filter returns a different number of rows with each function whenever ties exist.
• Use ROW_NUMBER for an exact row count, RANK when tied rows should share a place, and DENSE_RANK for questions about distinct values.
• ROW_NUMBER does not define which peer comes first, so add tiebreaker columns until the ORDER BY is unique.
• NTILE splits a partition into groups of nearly equal size by position, so it can place peers in different groups.

Ranking functions ignore the frame and always use the whole partition. Functions like FIRST_VALUE, LAST_VALUE, and a moving average do read a frame, and the next lesson, Window Frames and Running Totals, compares ROWS and RANGE frames on daily order counts.

Practice Questions

1. A show has five episodes with 900, 900, 700, 700, and 500 plays. What do ROW_NUMBER, RANK, and DENSE_RANK return when ordered by plays descending?

<details> <summary>Show answer</summary>

ROW_NUMBER returns 1 to 5, RANK returns 1, 1, 3, 3, 5, and DENSE_RANK returns 1, 1, 2, 2, 3. RANK counts the rows before each peer group, so the 700 episodes get 3 because two rows come before them. DENSE_RANK counts the distinct values before them, which is one, so they get 2. The 500 episode gets 5 from RANK and 3 from DENSE_RANK.

</details>

2. A product manager wants exactly three episodes per show, and the result must be the same every time the query runs. Which function and ORDER BY do you use?

<details> <summary>Show answer</summary>

ROW_NUMBER, with ORDER BY columns that are unique within each show. ROW_NUMBER guarantees one number per row, so <= 3 returns at most three rows per show. Ending the ORDER BY with a unique column, like episode_id, removes peers, so the same rows win on every run.

</details>

3. In the Byte Size show, which episodes does RANK() ... <= 2 return, and why is there no rank 2?

<details> <summary>Show answer</summary>

It returns episodes 201 and 203, both with rank 1. They tie at 6100 plays. RANK gives the next episode, 202, the number 3, because two rows come before it. No row has rank 2, so the filter returns two rows, and 202 is left out.

</details>

4. A query adds ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to a RANK window. Does the result change?

<details> <summary>Show answer</summary>

No, MySQL ignores the frame for RANK. The manual says ranking functions accept a frame clause but use the entire partition. The frame only matters for aggregate window functions and for FIRST_VALUE, LAST_VALUE, and NTH_VALUE.

</details>

5. Someone finds the most played episode of each show by joining the table to MAX(plays) per show. Which ranking filter returns the same rows?

<details> <summary>Show answer</summary>

RANK = 1, or DENSE_RANK = 1. The join keeps every episode whose plays equal the maximum, so a tie for first returns both episodes, as with 201 and 203 in Byte Size. Both rank functions give all peers at the top the number 1. ROW_NUMBER = 1 would return only one of them.

</details>