CODEWITHSUNDEEP
mysqldistinct
user3187554
user3187554

Reputation: 13

Most recent distinct record from a joined MySQL table

I have two tables, one of a list of competition results, and one with ELO ratings (based on previous competitions). Fetching the list of competitors for an arbitrary competition is trivial enough, but I also need to get the most recent rating value for them.

score:

id | eventid | competitorid | position
1    1         1              1
2    1         2              2
3    1         3              3
4    2         2              1
5    2         3              2
6    3         1              1
7    3         3              2
8    3         2              3

rating:

id | competitorid | rating
1    1              1600
2    2              1500
3    3              1500
4    2              1600
5    3              1590

Expected output for a query against score.eventid = 3 would be

id | competitorid | position | rating
6    1              1          1600
7    3              2          1590
8    2              3          1600

At the moment my code looks like:

SELECT score.scoreID, score.competitorID, score.position,
rating.id, rating.rating
FROM score, rating
WHERE score.competitorid = rating.competitorid
AND score.eventid = 3
ORDER BY score.position

which gives an output of

id | competitorid | position | rating.id | rating
6    1              1          1          1600
7    3              2          2          1500
7    3              2          4          1590
8    2              3          3          1500
8    2              3          5          1600

basically it's showing the data from the score table for that correct event, but giving me a row for every rating available against that competitorID unfortunately I have no idea where to build in the DISTINCT statement or how to limit it to the most recent result.

MySQL noob, and managed DISTINCT statements, but not with joins. Unfortunately most previous questions seemed to deal with getting distinct results from a single table, which is not quite what I'm after. Thanks!

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

One way to get the rating is with a correlated subquery:

SELECT s.scoreID, s.eventID, s.competitorID, s.position,
       (select r.rating
        from rating r
        where s.competitorID = r.competitorID
        order by r.id desc
        limit 1
       ) as rating
FROM score s
WHERE s.eventID = 3
ORDER BY s.position;

I'm not sure what ratingprid is, so this only includes the rating.

Upvotes: 1

Related Questions