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

-
Can you add which database you are actually using (vendor/version)... SQLServer, DB2, MySQL, Oracle, etc. –  rolfl Mar 7 at 16:41
Updated answer to include PostgreSQL –  rolfl Mar 7 at 17:48
Rolled back Rev 8 to Rev 7. (Please don't edit questions in a way that invalidates answers.) –  200_success Mar 7 at 18:16
@200_success: Why does Revision 7 invalidate answers? –  miracle173 Jun 26 at 7:06

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

-
In my experience, CTEs are slower than using a subquery since it is about the same as creating a temporary table (ie. you lose your indexes). –  cimmanon Mar 7 at 21:18

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)

-
Had a similar answer, but what if more than 1 person have the top-count? –  konijn Mar 7 at 16:50
Good question... @user35265 - what konijn says.... ? –  rolfl Mar 7 at 16:51
If there is more than one athlete with the same medal count in my query I believe it would select all athletes with the medal count –  The Bear Mar 7 at 17:03
The runtime of your new sql query is 66.962 ms while the runtime of my query is 38.919 ms. –  The Bear Mar 7 at 17:56
@user35265 if you really need better performance on this query then you should consider a separate table with pre-computed aggregations ... perhaps a materialized view... or a table that pre-aggregates the data.... –  rolfl Mar 7 at 18:06

I'm a little late to the party, but I think you were all over-complicating this...

Wouldn't this be what you need:

SELECT athlete
FROM Olympics
GROUP BY athlete
ORDER BY SUM(total_medals) DESC
LIMIT 1


Here is the obligitory SQL Fiddle.

EDIT: Previous version didn't account for multiple people with the same number of medals.

SELECT athlete
FROM Olympics
GROUP BY athlete
HAVING SUM(total_medals) =
(
SELECT SUM(total_medals)
FROM Olympics
GROUP BY athlete
ORDER BY SUM(total_medals) DESC
LIMIT 1
)


At a quick glance, the execution plan for this seems a little nicer than the other suggestions, feel free to correct me if I am wrong though.

Here is the SQL Fiddle.

-
This isn't correct because you could have more than one athlete that has the "highest" number of medals –  The Bear Aug 20 at 2:06
@TheBear Thanks, I think I misread the question, I've updated the answer now. –  PenutReaper Aug 20 at 9:33

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.

-
+1 for mentioning the hard to read formatting –  RubberDuck Aug 20 at 12:34