Highest Batting Partnership in Each T20 Match

Medium
457.

Highest Batting Partnership in Each T20 Match

MediumWindow Functions

MySQL

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

ColumnType
match_idint
innings_noint
wicket_noint
batter_onevarchar
batter_twovarchar
runsint
ballsint

Primary key (together): match_id, innings_no, wicket_no.

One row per partnership. innings_no is 1 or 2.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Partnership

match_idinnings_nowicket_nobatter_onebatter_tworunsballs
40111Rohan IyerKabir Khan6841
40112Kabir KhanArjun Nair2215
40121Dev MehtaVikram Rao9150
40122Vikram RaoIshaan Bose149
40211Arjun NairRohan Iyer4028
40213Kabir KhanFarhan Patel5530
40221Dev MehtaHarsh Joshi5537
40311Ishaan BoseVikram Rao1210

Output

match_idinnings_nowicket_nobattersruns
40121Dev Mehta & Vikram Rao91
40213Kabir Khan & Farhan Patel55
40221Dev Mehta & Harsh Joshi55
40311Ishaan Bose & Vikram Rao12
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
match_idint
innings_noint
wicket_noint
batter_onevarchar
batter_twovarchar
runsint
ballsint

Primary key: match_id, innings_no, wicket_no.

One row per partnership. innings_no is 1 or 2.

Examples

Example 1

Partnership

match_idinnings_nowicket_nobatter_onebatter_tworunsballs
40111Rohan IyerKabir Khan6841
40112Kabir KhanArjun Nair2215
40121Dev MehtaVikram Rao9150
40122Vikram RaoIshaan Bose149
40211Arjun NairRohan Iyer4028
40213Kabir KhanFarhan Patel5530
40221Dev MehtaHarsh Joshi5537
40311Ishaan BoseVikram Rao1210

Output

match_idinnings_nowicket_nobattersruns
40121Dev Mehta & Vikram Rao91
40213Kabir Khan & Farhan Patel55
40221Dev Mehta & Harsh Joshi55
40311Ishaan Bose & Vikram Rao12

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 = 1

Another 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.runs

Another 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 →