Q:

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);

A:

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

A:

In PostgreSQL, you can use the rank() mechanism to help.
It still requires a subselect, but consider the following query:
Select o.athlete,
       sum(o.total_medals) as sumtotal_medals
from Olympics o,
     ( select r.athlete as toprank,
              rank() over ( order by sum(r.total_medals) desc ) as rank
       from Olympics r
       group by r.athlete
     ) rankings
where o.athlete = rankings.toprank
  and rankings.rank = 1
group by o.athlete
order by o.athlete

I have put this in to the SQLFiddle here....

Previous MySQL exampl
This can be done as top-count with a grouped select with a having clause.
Select TOP 1 athlete
from Olympics
group by athlete
order by Sum(total_medals) DESC

if you want the actual medal haul, add the sum to the select.
Select TOP 1 athlete, Sum(total_medals) as total_medals
from Olympics
group by athlete
order by Sum(total_medals) DESC

I have put together a fiddle using MySQL (which has the LIMIT key-word)

A:

I think that you are over thinking this,  in SQL Server I would do something like this
SELECT TOP (10) athlete  
FROM ( SELECT athlete, Sum(total_medals) AS total_medals
       FROM Olympics
       ORDER BY total_medals DESC
       GROUP BY athlete) 

And then I would use my Reporting Software to decide if there are 2 or more people at the top.
This is probably more of what you want anyway, a top 10 list of all time.

Side Note
I found it rather difficult to read your query because it wasn't indented and the reserved words weren't capitalized.  I would recommend that you do those things when writing a query.

