patternsqlModerate
Finding person with most medals in Olympics database
Viewed 0 times
personolympicswithdatabasefindingmedalsmost
Problem
I have an Olympics database from each Olympic year and I want to find the person that has won the most medals. The main problem is that I'm basically querying the same sub-query twice in
SUBSET1 and SUBSET2. How would I go about making this more efficient?Select athlete FROM ( Select athlete, Sum(total_medals) as total_medals
from Olympics Group by athlete) as SUBSET1 Where total_medals =
( Select Max( total_medals ) FROM ( Select Sum(total_medals) as total_medals
from Olympics Group by athlete ) as SUBSET2);Solution
This alternative to @rolfl's answer is more readable, in my opinion. It also has a more efficient execution plan.
SQLFiddle
WITH medal_count AS (
SELECT athlete
, SUM(total_medals) AS grand_total_medals
, RANK() OVER (ORDER BY SUM(total_medals) DESC) AS rank
FROM Olympics
GROUP BY athlete
)
SELECT athlete
, grand_total_medals
FROM medal_count
WHERE rank = 1
ORDER BY athlete;SQLFiddle
Code Snippets
WITH medal_count AS (
SELECT athlete
, SUM(total_medals) AS grand_total_medals
, RANK() OVER (ORDER BY SUM(total_medals) DESC) AS rank
FROM Olympics
GROUP BY athlete
)
SELECT athlete
, grand_total_medals
FROM medal_count
WHERE rank = 1
ORDER BY athlete;Context
StackExchange Code Review Q#43721, answer score: 11
Revisions (0)
No revisions yet.