# Limit join to one per row

Say I have many players, each with a numerical rating, stored in a ratings table that keeps track of both current and historical ratings (i.e. it has player_id, rating, and date columns).

How would I go about fetching the current rating of each player? As in, the rating with the most recent date.

select * from players join ratings on ratings.player_id=players.id would fetch every rating a player has ever had... do I need a group by in there? I'm struggling to see how though.

• Please always include the version of your server! This criterion is especially important for MySQL which has made several important improvements recently! – Vérace Jan 14 at 7:19

This is a classic greatest-n-per-group problem. I would urge you to look at that link (and also here - search for "Within-group quotas (Top N per group)") to learn about this important SQL functionality! That second link's entire page is a great resource for learning SQL generally - plus it's specific to MySQL!

In order to solve your particular problem (n = 1), I did the following (for versions < 8, see the fiddle here - see below for 8):

Create and populate a rating table:

CREATE TABLE rating
(
rating_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
player_id INTEGER NOT NULL,  -- have FOREIGN KEY to player table!
rating TINYINT UNSIGNED NOT NULL,
rating_date DATE NOT NULL
);

And populate it:

INSERT INTO rating (player_id, rating, rating_date)
VALUES
(1,  3, '2013-09-23'),
(1,  4, '2016-04-07'),
(1,  5, '2018-11-17'),
(2,  8, '2015-09-23'),
(2,  7, '2017-09-23'),
(2,  6, '2020-01-03'),
(3, 15, '2011-05-30'),
(3, 17, '2012-02-11'),
(3, 14, '2014-12-14');

## 1st query:

Get each player_id and the MAX() of their rating date, which I'm implicitly assuming is their current rating. You may want an is_still_playing field in the player table to take account of retired players - see the edit below for joining with the player table!

SELECT r.player_id, MAX(r.rating_date) AS current_rating_date
FROM rating r
GROUP BY r.player_id;
ORDER BY r.player_id; -- Always have an ORDER BY clause!

Result:

player_id   current_rating_date
1            2018-11-17
2            2020-01-03
3            2014-12-14

Inspection shows that this is correct.

Then we JOIN this query (or table) back to the ratings table but this time, we include the rating:

SELECT r2.player_id, r2.rating, r2.rating_date
FROM rating r2
JOIN
(
SELECT r1.player_id, MAX(r1.rating_date) AS current_rating
FROM rating r1
GROUP BY r1.player_id
) AS t
ON r2.player_id = t.player_id AND
r2.rating_date = t.current_rating
ORDER BY r2.player_id;

Result:

player_id   rating  current_rating
1        5      2018-11-17
2        6      2020-01-03
3       14      2014-12-14

Which, again, we can see by inspection is the correct answer.

## 2nd Query:

If you are running version 8 of MySQL, it is even easier using the ROW_NUMBER() Window function (see the fiddle here):

First, demo of ROW_NUMBER() functionality:

SELECT
r1.player_id, r1.rating, r1.rating_date,
ROW_NUMBER()
OVER (PARTITION BY r1.player_id ORDER BY r1.rating_date DESC) AS rn
FROM rating r1
ORDER BY r1.player_id, r1.rating_date;

Result:

player_id   rating  rating_date rn
1        3  2013-09-23   3
1        4  2016-04-07   2
1        5  2018-11-17   1 -- << want this one
2        8  2015-09-23   3
2        7  2017-09-23   2
2        6  2020-01-03   1 -- << want this one
3       15  2011-05-30   3
3       17  2012-02-11   2
3       14  2014-12-14   1 -- << want this one

So we place the results of this query in a subselect as follows:

SELECT t.player_id, t.rating, t.rating_date AS current_rating
FROM
(
SELECT
r1.player_id, r1.rating, r1.rating_date,
ROW_NUMBER()
OVER (PARTITION BY r1.player_id ORDER BY r1.rating_date DESC) AS rn
FROM rating r1
ORDER BY r1.player_id, r1.rating_date
) AS t
WHERE t.rn = 1
ORDER BY t.player_id;

The final result of the players' ratings is the same as above for version 5.6 of MySQL.

Window functions are very powerful and well worth getting to know - definitely worth the upgrade! It's also the reason why it's (again) very important to include your version of MySQL - all the more so with this particular RDBMS since many significant new features have been added recently.

## EDIT:

I noticed that I hadn't quite answered the full question - joining to the player table (see fiddle here - works for 5.6 onwards).

CREATE TABLE player  -- could have many more fields (contact, height...)
(
player_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
player_name VARCHAR (50) NOT NULL,
player_dob DATE NOT NULL,
player_ssn VARCHAR (10) NOT NULL
);
INSERT INTO player (player_name, player_dob, player_ssn)
VALUES
('Joe Sixpack',  '1995-10-15', 'X123456789'),
('Seán Citizen', '1994-02-02', 'Y987654321'),
('Bill Person',  '1997-12-20', 'Z567891234');

And then run this:

-- Joining the above result to the player's details from the `player` table

SELECT
p.player_id   AS p_pid,  p.player_name, p.player_dob, player_ssn,
r_1.player_id AS r_pid,  r_1.rating, r_1.current_rating
FROM player p
JOIN
(
SELECT r2.player_id, r2.rating, r2.rating_date AS current_rating
FROM rating r2
JOIN
(
SELECT r1.player_id, MAX(r1.rating_date) AS current_rating
FROM rating r1
GROUP BY r1.player_id
) AS t
ON r2.player_id = t.player_id AND
r2.rating_date = t.current_rating
ORDER BY r2.player_id
) AS r_1
ON p.player_id = r_1.player_id
ORDER BY p.player_id; -- only 1 record per player so no point in ordering by further fields

Result:

p_pid  player_name  player_dob  player_ssn  r_pid   rating  current_rating
1  Joe Sixpack  1995-10-15  X123456789      1        5      2018-11-17
2 Seán Citizen  1994-02-02  Y987654321      2        6      2020-01-03
3  Bill Person  1997-12-20  Z567891234      3       14      2014-12-14

Note that the player_id occurs twice in the result set above - you won't want that for the final query - I just left both in so that the JOINing logic would be very clear! Also (and this is just a matter of personal preference), note that I use singular table names - a table is a set and therefore singular. You can find many SQL coding standards on the web - pick one and stick to it - it really does help when trying to debug!

If you want strict semantics you need to double join: Once to find the selector (i.e. the most current rating) and a second time to fetch the rating:

SELECT
baseview.*.
ratings.rating
FROM (
SELECT
players.*,
MAX(`date`) AS ratingdate
FROM
players
INNER JOIN ratings ON ratings.player_id=players.id
GROUP BY players.id
) AS baseview
INNER JOIN ratings
ON baseview.id=ratings.player_id
AND baseview.ratingdate=ratings.date
;