title: How to compose MySQL query
body: <p>I have this table in a MySQL database.</p>
  
  <pre><code>-----------------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------
  -------------------------------------
  </code></pre>
  
  <p>I want to have this output.</p>
  
  <pre><code>---------------output----------------
  ----eventId----athleteId----score----
  ----1----------129907-------900------
  ----2----------492561-------960------
  ----3----------328992-------936------
  ----4----------//////-------///------
  ----5----------487422-------900------
  ----6----------138688-------950------
  ----7----------629876-------940------
  </code></pre>
  
  <p>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.</p>
  
  <p>Shortened: one athlete can't appear two times in the result. </p>
  
  <pre><code>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
  </code></pre>