-2

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.

  • 1
    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
2

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!

0

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.