Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > select version(); > > <pre> > | version() | > | :-------- | > | 5.6.47 | > </pre> <!-- --> > 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'); > SELECT * FROM player; > > <pre> > ✓ > > ✓ > > player_id | player_name | player_dob | player_ssn > --------: | :------------ | :--------- | :--------- > 1 | Joe Sixpack | 1995-10-15 | X123456789 > 2 | Seán Citizen | 1994-02-02 | Y987654321 > 3 | Bill Person | 1997-12-20 | Z567891234 > </pre> <!-- --> > 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 > ); > > <pre> > ✓ > </pre> <!-- --> > 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'); > SELECT * FROM rating; > > <pre> > ✓ > > rating_id | player_id | rating | rating_date > --------: | --------: | -----: | :---------- > 1 | 1 | 3 | 2013-09-23 > 2 | 1 | 4 | 2016-04-07 > 3 | 1 | 5 | 2018-11-17 > 4 | 2 | 8 | 2015-09-23 > 5 | 2 | 7 | 2017-09-23 > 6 | 2 | 6 | 2020-01-03 > 7 | 3 | 15 | 2011-05-30 > 8 | 3 | 17 | 2012-02-11 > 9 | 3 | 14 | 2014-12-14 > </pre> <!-- --> > 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! > > <pre> > player_id | current_rating_date > --------: | :------------------ > 1 | 2018-11-17 > 2 | 2020-01-03 > 3 | 2014-12-14 > > ✓ > </pre> <!-- --> > 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; > > <pre> > player_id | rating | current_rating > --------: | -----: | :------------- > 1 | 5 | 2018-11-17 > 2 | 6 | 2020-01-03 > 3 | 14 | 2014-12-14 > </pre> <!-- --> > -- 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 > > <pre> > 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 > > ✓ > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=ac59b53f05fff9fa81fc2796820e78f9)*
back to fiddle