Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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);
share|improve this question
    
Can you add which database you are actually using (vendor/version)... SQLServer, DB2, MySQL, Oracle, etc. –  rolfl Mar 7 at 16:41
1  
Updated answer to include PostgreSQL –  rolfl Mar 7 at 17:48
2  
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

4 Answers 4

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

share|improve this answer
    
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)

share|improve this answer
    
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
1  
@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.

share|improve this answer
1  
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.

share|improve this answer
    
+1 for mentioning the hard to read formatting –  RubberDuck Aug 20 at 12:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.