MySQL 5.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.6.51


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 
);
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;

Records: 9  Duplicates: 0  Warnings: 0




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


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!


player_id
current_rating_date


1
2018-11-17

2
2020-01-03

3
2014-12-14


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;


player_id
rating
current_rating


1
5
2018-11-17

2
6
2020-01-03

3
14
2014-12-14

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