15
\$\begingroup\$

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);
\$\endgroup\$
6
  • \$\begingroup\$ Can you add which database you are actually using (vendor/version)... SQLServer, DB2, MySQL, Oracle, etc. \$\endgroup\$
    – rolfl
    Mar 7 '14 at 16:41
  • 1
    \$\begingroup\$ Updated answer to include PostgreSQL \$\endgroup\$
    – rolfl
    Mar 7 '14 at 17:48
  • 3
    \$\begingroup\$ Rolled back Rev 8 to Rev 7. (Please don't edit questions in a way that invalidates answers.) \$\endgroup\$ Mar 7 '14 at 18:16
  • \$\begingroup\$ @200_success: Why does Revision 7 invalidate answers? \$\endgroup\$
    – miracle173
    Jun 26 '14 at 7:06
  • \$\begingroup\$ @miracle173, because Malachi had already written "difficult to read ... because ... the reserved words weren't capitalized" (presumably meaning upcased rather than capitalized, but you get the meaning. \$\endgroup\$ Feb 21 '18 at 16:01
11
\$\begingroup\$

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

\$\endgroup\$
1
  • \$\begingroup\$ 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). \$\endgroup\$
    – cimmanon
    Mar 7 '14 at 21:18
10
\$\begingroup\$

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)

\$\endgroup\$
10
  • \$\begingroup\$ Had a similar answer, but what if more than 1 person have the top-count? \$\endgroup\$
    – konijn
    Mar 7 '14 at 16:50
  • \$\begingroup\$ Good question... @user35265 - what konijn says.... ? \$\endgroup\$
    – rolfl
    Mar 7 '14 at 16:51
  • \$\begingroup\$ 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 \$\endgroup\$
    – The Bear
    Mar 7 '14 at 17:03
  • \$\begingroup\$ The runtime of your new sql query is 66.962 ms while the runtime of my query is 38.919 ms. \$\endgroup\$
    – The Bear
    Mar 7 '14 at 17:56
  • 1
    \$\begingroup\$ @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.... \$\endgroup\$
    – rolfl
    Mar 7 '14 at 18:06
6
\$\begingroup\$

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.

\$\endgroup\$
0
5
\$\begingroup\$

I'm a little late to the party, but I think you can make it less complicated.

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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