clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 818551 fiddles created (9197 in the last week).

select version();
version()
8.0.19
 hidden batch(es)


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 );
 hidden batch(es)


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;
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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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;
player_id rating current_rating
1 5 2018-11-17
2 6 2020-01-03
3 14 2014-12-14
 hidden batch(es)