Second-Best Innings in the College Cup — SQL Medium Problem

Problem statement

The inter-college cricket cup records one row per batting innings. The organisers give a prize for the top score and a smaller one for the second-highest distinct score — if two batters share the top score, the runner-up prize goes to the next lower score.

Return one row with one column, second_highest_runs: the second-highest distinct value of runs. A NULL runs means the player did not bat and is not a score. If there are fewer than two distinct scores, return one row holding NULL.

Tables

Table: BattingCard

Primary key: innings_id.

One row per innings. runs is NULL when the player was listed but did not bat.

Examples

Example 1

BattingCard

Output

Example 2

BattingCard

Output

How to solve Second-Best Innings in the College Cup

"Second-highest" means second among the distinct scores: if 87 appears twice, the answer is the next lower value, not 87 again. So the scores are deduplicated first, sorted high to low, and the second one is taken — SELECT DISTINCT runs … ORDER BY runs DESC LIMIT 1 OFFSET 1.

On its own that query returns no row when there is no second score, while the statement wants one row holding NULL. Putting it inside a scalar subquery — SELECT ( … ) AS second_highest_runs — fixes that: a scalar subquery that finds nothing evaluates to NULL, and the outer SELECT without a FROM always yields exactly one row.

The aggregate version needs no LIMIT: the second-highest distinct value is MAX(runs) over the rows whose runs are below the overall MAX(runs). An aggregate without GROUP BY also always returns one row, NULL when nothing qualifies, so the empty case comes for free. DENSE_RANK() gives the same result (rank 2 is the second distinct value), and generalises to the N-th highest; a correlated count of distinct higher scores does too, at quadratic cost.

NULL runs never win: MAX ignores them and < against NULL is unknown. The sort-based queries filter them out explicitly.

Reference solution (MySQL)

SELECT (
  SELECT DISTINCT runs
  FROM BattingCard
  WHERE runs IS NOT NULL
  ORDER BY runs DESC
  LIMIT 1 OFFSET 1
) AS second_highest_runs

Another way

SELECT MAX(runs) AS second_highest_runs FROM BattingCard WHERE runs < (SELECT MAX(runs) FROM BattingCard)

Another way

SELECT MAX(runs) AS second_highest_runs
FROM (SELECT runs, DENSE_RANK() OVER (ORDER BY runs DESC) AS rnk FROM BattingCard WHERE runs IS NOT NULL) ranked
WHERE rnk = 2

Another way

SELECT MAX(b.runs) AS second_highest_runs
FROM BattingCard b
WHERE (SELECT COUNT(DISTINCT c.runs) FROM BattingCard c WHERE c.runs > b.runs) = 1