adorp adorp - 3 years ago 163
MySQL Question

An interesting SQL query CHALLENGE - list the AVERAGE value of the top 3 scores of each athlete

enter image description here


An interesting SQL query CHALLENGE:

A table named athelets consisting of id, ath_id, name, score, date.

+----+--------+-----------------+--------+------------+
| id | ath_id | name | record | r_date |
+----+--------+-----------------+--------+------------+
| 1 | 2 | John Wayne | 79 | 2010-07-08 |
| 2 | 7 | Ronald Regan | 51 | 2000-03-22 |
| 3 | 1 | Ford Harrison | 85 | 2009-11-13 |
| 4 | 2 | John Wayne | 69 | 2017-01-01 |

Please write a sql query to list the average value of the top three scores of each athlete, something like:

ath_id: 1, the arithmetic mean of his/her top 3 records: 77
ath_id: 2, the arithmetic mean of his/her top 3 records: 73
ath_id: 3, the arithmetic mean of his/her top 3 records: 47

Answer Source
select ath_id, avg(record)
   from 
     (select ath_id, record
       from atheletes as t1
        where 
        (select count(*) from atheletes  where t1.ath_id=ath_id and record > t1.record) < 3) as d
 group by ath_id;

The above query should works as expected.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download