I want the last row of all players in a specific month+year

Question

Here is a simplified version of my sql table of 2 months (ORDERED BY DATE):

player_id | date            | score
1               2011-05-25   1200
2               2011-05-25   3400
3               2011-05-26   3200
4               2011-05-26   4400
1               2011-05-28   1000
2               2011-05-28   2000
3               2011-05-29   3000
4               2011-05-29   4000
1               2011-06-24   1300
2               2011-06-24   2500
3               2011-06-24   5000
4               2011-06-24   3000

Basically, I want a query that shows the last score of all players in a specific month/specific year.

Example:

If I want the final scores of all players in the month 05, te result would be:
1    2011-05-28    1000
2    2011-05-28    2000
3    2011-05-29    3000
4    2011-05-29    4000

My sql query so far:

  SELECT m1.* FROM table m1 
    LEFT JOIN table m2 ON (m1.player_id = m2.player_id AND m1.date < m2.date) 
    WHERE m2.date IS NULL 
    AND month(m1.date) = 05 
    AND year(m1.date) = 2011 
    ORDER BY score DESC);

This doesn't seem to show all players, only players that didn't play in the months after 05. Where do I add the date select?

**EDIT John Nestoriak's answer bellow did the trick for me :)

I'm not sure I understand, what are the 2 tables that you're `JOIN`-ing? You explained only 1 table. — cha0site, Feb 02 '12 at 22:35
@cha0site he is joining the table on itself. And I think you might just need to change your `<` in the join to `>`. Trying to remember, but I believe you want the max from the first table. — Jonathan Kuhn, Feb 02 '12 at 22:39
Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — mkrieger1, Jan 21 '20 at 18:37

score 3 · Accepted Answer · edited May 23 '17 at 11:48

I think he's referring to the technique shown here: Retrieving the last record in each group

With the additional constraint of he doesn't want the last record but the last record in a given month.

Oddly enough you have to give that additional constraint twice, once in the join condition and again to filter the results. This should do it for you.

SELECT m1.* FROM table m1 
  LEFT JOIN table m2 ON 
    (m1.player_id = m2.player_id AND m1.date < m2.date 
     AND m2.date < '2011-06-01') 
  WHERE m2.date IS NULL AND month(m1.date) = 5 AND year(m1.date) = 2011

score 1 · Answer 2 · answered Feb 03 '12 at 00:17

Assuming that the (player_id, date) combination in Unique:

SELECT 
      t.*
FROM 
      TableX AS t
  JOIN
      ( SELECT 
              player_id
            , MAX(date) AS maxDate
        FROM
            TableX
        WHERE 
             date BETWEEN '2011-05-01'
                      AND LAST_DAY('2011-05-01')
        GROUP BY
              player_id
      ) AS tg
    ON  
      (tg.player_id, tg.maxDate) = (t.player_id, t.date)
ORDER BY 
      t.score DESC

I want the last row of all players in a specific month+year

2 Answers2