StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to compose MySQL query
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload