Highest Batting Partnership in Each T20 Match — SQL Problem
A partnership is the runs two batters add together while they are at the crease, recorded by the wicket that ended it (wicket_no 1 is the opening stand).
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #457
Problem statement
A partnership is the runs two batters add together while they are at the crease, recorded by the wicket that ended it (wicket_no 1 is the opening stand). The commentary team wants the best stand of every match, across both innings.
Return, for every match, the partnership(s) with the most runs in that match — if several share the top total, return all of them. Columns: match_id, innings_no, wicket_no, batters (the two names joined as batter_one & batter_two, for example Rohan Iyer & Kabir Khan) and runs. Return the rows in any order.
Tables
Table: Partnership
| Column | Type |
|---|---|
| match_id | int |
| innings_no | int |
| wicket_no | int |
| batter_one | varchar |
| batter_two | varchar |
| runs | int |
| balls | int |
Primary key: match_id, innings_no, wicket_no.
One row per partnership. innings_no is 1 or 2.
Examples
Example 1
Partnership
| match_id | innings_no | wicket_no | batter_one | batter_two | runs | balls |
|---|---|---|---|---|---|---|
| 401 | 1 | 1 | Rohan Iyer | Kabir Khan | 68 | 41 |
| 401 | 1 | 2 | Kabir Khan | Arjun Nair | 22 | 15 |
| 401 | 2 | 1 | Dev Mehta | Vikram Rao | 91 | 50 |
| 401 | 2 | 2 | Vikram Rao | Ishaan Bose | 14 | 9 |
| 402 | 1 | 1 | Arjun Nair | Rohan Iyer | 40 | 28 |
| 402 | 1 | 3 | Kabir Khan | Farhan Patel | 55 | 30 |
| 402 | 2 | 1 | Dev Mehta | Harsh Joshi | 55 | 37 |
| 403 | 1 | 1 | Ishaan Bose | Vikram Rao | 12 | 10 |
Output
| match_id | innings_no | wicket_no | batters | runs |
|---|---|---|---|---|
| 401 | 2 | 1 | Dev Mehta & Vikram Rao | 91 |
| 402 | 1 | 3 | Kabir Khan & Farhan Patel | 55 |
| 402 | 2 | 1 | Dev Mehta & Harsh Joshi | 55 |
| 403 | 1 | 1 | Ishaan Bose & Vikram Rao | 12 |
How to solve Highest Batting Partnership in Each T20 Match
This is top-1 per group with ties. RANK() OVER (PARTITION BY match_id ORDER BY runs DESC) numbers the partnerships within each match from the biggest down, and two stands with equal runs get the same rank. Keeping rank 1 returns every partnership equal to the match's best — in match 402 both 55-run stands, one in each innings. ROW_NUMBER would pick one of the two arbitrarily, which is wrong here; DENSE_RANK would also work for rank 1.
The partition is the match, not the innings: an outstanding first-innings stand can beat everything in the chase, and the question is about the match as a whole.
Two classic alternatives: join to the per-match MAX(runs) and keep the rows that equal it, or keep a partnership when no other in its match has more runs (NOT EXISTS). All three return ties. CONCAT builds the display name; neither name column is ever NULL. The window plan is one sort by match and runs.
Reference solution (MySQL)
SELECT match_id, innings_no, wicket_no, CONCAT(batter_one, ' & ', batter_two) AS batters, runs
FROM (
SELECT p.*, RANK() OVER (PARTITION BY match_id ORDER BY runs DESC) AS rnk
FROM Partnership p
) ranked
WHERE rnk = 1Another way
SELECT p.match_id, p.innings_no, p.wicket_no, CONCAT(p.batter_one, ' & ', p.batter_two) AS batters, p.runs FROM Partnership p JOIN (SELECT match_id, MAX(runs) AS best FROM Partnership GROUP BY match_id) b ON b.match_id = p.match_id AND b.best = p.runsAnother way
SELECT match_id, innings_no, wicket_no, CONCAT(batter_one, ' & ', batter_two) AS batters, runs FROM Partnership p WHERE NOT EXISTS (SELECT 1 FROM Partnership q WHERE q.match_id = p.match_id AND q.runs > p.runs)← Football League Table With Points and Goal Difference · Best Bowling Figures of Each Bowler This Season →