HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Finding person with most medals in Olympics database

Submitted by: @import:stackexchange-codereview··
0
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.

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.