How to compose MySQL query

Question

I have this table in a MySQL database.

-----------------tests---------------
----athleteId----eventId----score----
----129907-------1----------900------
----129907-------2----------940------
----129907-------3----------927------
----129907-------4----------856------
----328992-------1----------780------
----328992-------2----------890------
----328992-------3----------936------
----328992-------4----------864------
----492561-------1----------899------
----492561-------2----------960------
----492561-------3----------840------
----492561-------4----------920------
----487422-------5----------900------
----487422-------6----------940------
----487422-------7----------927------
----629876-------5----------780------
----629876-------6----------890------
----629876-------7----------940------
----138688-------5----------899------
----138688-------6----------950------
----138688-------7----------840------
-------------------------------------

I want to have this output.

---------------output----------------
----eventId----athleteId----score----
----1----------129907-------900------
----2----------492561-------960------
----3----------328992-------936------
----4----------//////-------///------
----5----------487422-------900------
----6----------138688-------950------
----7----------629876-------940------

We partially solved the problem with this query, but I'd like to have only 1 distinct athleteId for each eventId. At the moment, if the best performance for 2 events is done by the same athlete, that athlete will appear in the output two times. If this happens, I need the athlete with the 2ND best performance to appear instead of the first.

Shortened: one athlete can't appear two times in the result.

SELECT athleteId, a.eventId, a.score
FROM tests AS a
JOIN (
-- This select finds the top score for each event
SELECT eventId, MAX(score) AS score
FROM tests 
GROUP BY eventId
) AS b
-- Join on the top scores
ON a.eventId = b.eventId
AND a.score = b.score

Looks like you have a [tag:greatest-n-per-group] problem. Look here for a solution: http://stackoverflow.com/a/7745635/570191 — Adriano Carneiro, Dec 28 '12 at 18:17
@AdrianCarneiro he is looking for the second highest per group not the highest. — Mahmoud Gamal, Dec 28 '12 at 18:23
And if the same two athletes are 1st and 2nd in 3 events, do you need the 3rd best for the third event? — Barmar, Dec 28 '12 at 18:37
This will probably be much easier to implement in caller code than in SQL. — Barmar, Dec 28 '12 at 18:39
When an athlete wins two events, how do you choose which to show? You have the possibility of an event where all athletes have won another event, so that event has no valid winners. This is a hard problem. — Gordon Linoff, Dec 28 '12 at 19:25

Barmar · Accepted Answer · 2012-12-28T20:22:59.660

1

Here's how to do it in caller code (PHP in this example).

Use the query:

SELECT athleteId, eventId, score
FROM tests
ORDER BY score DESC;

Then use the following code to process the result of the query (I'm skipping all the boilerplate to perform the query):

$events = array(); // Remember events reported
$athletes = array(); // Remember athletes listed

while ($row = mysqli_fetch_assoc($results)) {
  if (isset($events[$row['eventId']]) || isset($athletes[$row['athleteId']])) {
     continue;
  }
  $events[$row['eventId']] = true;
  $athletes[$row['athleteId']] = true;
  print_row($row);
}

edited Dec 28 '12 at 20:22

answered Dec 28 '12 at 18:50

Barmar

741,623
53
500
612

I'm checking it. Will report soon! – djpredator17 Dec 28 '12 at 19:30
It works, thank you. What if I need to have male/female athletes? Should I add a $sex array? – djpredator17 Dec 28 '12 at 19:51
For a top male and female in each event: Make `$events` a 2-D array, with the 2nd dimension being sex. Then check for `$events[$row['eventId]][$row['sex']]`. – Barmar Dec 28 '12 at 20:21

How to compose MySQL query

1 Answers1