Finding person with most medals in Olympics database 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);
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