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

235

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

797

Answer

Solution:

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
505

Answer

Solution:

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

People are also looking for solutions to the problem: php - Severe High Memory Usage (Relative)

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.