Costliest Auction Buy of Each Cricket Team — SQL Medium Problem

Problem statement

At a college cricket league's auction, each player who is sold joins a team at the price the team bid; a player nobody bought has team_id NULL and keeps their base price.

For every team, return the player it paid the most for — and when several of its players went for the same top price, return all of them. Return the columns team (the team's name), player (the player's name) and price. Teams that bought nobody, and unsold players, do not appear. Return the rows in any order.

Tables

Table: Team

Primary key: team_id.

One row per franchise in the league.

Table: Player

Primary key: player_id.

team_id is the buying team, or NULL if unsold. price is in thousands of rupees.

Examples

Example 1

Team

Player

Output

How to solve Costliest Auction Buy of Each Cricket Team

"The top row per group, ties included" is answered in two steps. First compute the maximum price of each team with GROUP BY team_id. Then go back to Player and keep the rows whose team and price match one of those maxima. Comparing against the value — not picking a single row — is what keeps every player tied at the top.

The reference query does the matching with a row-value IN: (p.team_id, p.price) IN (SELECT team_id, MAX(price) …). The grouped subquery also produces a group for unsold players (team NULL), but a NULL never equals anything, so that pair matches nobody — and the join to Team would drop those players anyway. Teams with no players produce no group and so no row.

Three other shapes give the same answer. RANK() OVER (PARTITION BY team_id ORDER BY price DESC) numbers each team's players with ties sharing rank 1 (ROW_NUMBER would wrongly keep one). A correlated subquery compares each player with their own team's maximum. A join to a derived table of maxima is the grouped version written as a join. Each reads Player about twice; the window version reads it once and sorts per team.

Reference solution (MySQL)

SELECT t.team_name AS team, p.player_name AS player, p.price AS price
FROM Player p
JOIN Team t ON t.team_id = p.team_id
WHERE (p.team_id, p.price) IN (SELECT team_id, MAX(price) FROM Player GROUP BY team_id)

Another way

WITH ranked AS (
  SELECT team_id, player_name, price, RANK() OVER (PARTITION BY team_id ORDER BY price DESC) AS rk FROM Player
)
SELECT t.team_name AS team, r.player_name AS player, r.price AS price
FROM ranked r JOIN Team t ON t.team_id = r.team_id
WHERE r.rk = 1

Another way

SELECT t.team_name AS team, p.player_name AS player, p.price AS price FROM Player p JOIN Team t ON t.team_id = p.team_id WHERE p.price = (SELECT MAX(q.price) FROM Player q WHERE q.team_id = p.team_id)

Another way

SELECT t.team_name AS team, p.player_name AS player, p.price AS price FROM Player p JOIN (SELECT team_id, MAX(price) AS top FROM Player GROUP BY team_id) m ON m.team_id = p.team_id AND m.top = p.price JOIN Team t ON t.team_id = p.team_id