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. 809167 fiddles created (9785 in the last week).

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


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


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