hello,

I have table structure like below. Each row is one played game, each
person can play many or none times in each month.

id | person | score | date |
------------------------------------
1 | 32 | 444 | 2011-05 |
2 | 65 | 528 | 2011-05 |
3 | 77 | 455 | 2011-05 |
4 | 32 | 266 | 2011-06 |
5 | 77 | 100 | 2011-06 |
6 | 77 | 457 | 2011-06 |
7 | 77 | 457 | 2011-06 |
8 | 65 | 999 | 2011-07 |
9 | 32 | 222 | 2011-07 |
I am trying to get for each person sum of its best score in each
month. S result of above should be:

person | SUM(ofbestofeachmonth)
---------------------------------
32 | 932
65 | 1527
77 | 912
I know how to fetch the bests scores per userin month or some range


SELECT person, date, MAX(score) as 'sc' FROM tabgames WHERE
MONTH(date) = 6 GROUP BY person HAVING (sc>0)


Because i need in the end results of sum of max at quarter of year,
now i am fetching best for each month and outside the MySQL i am
adding them.

Now i am reading about group-wise max and still try to get excpected
results. Any help

TIA

Also on http://stackoverflow.com/questions/6...d-max-of-group

Jeff Carpereret <carpereret (AT) gmail (DOT) com> wrote:
That's a start. But why limit it to one month?

SELECT person, date, MAX(score) as 'sc'
FROM tabgames GROUP BY person, date

If you want that data for only one quarter then add a condition

WHERE date BETWEEN <first-month-of-quarter> AND <last-month-of-quarter>

This is not the groupwise maximum problem.

Simply use a derived table subquery based on the above query:

SELECT person, SUM(sc) FROM
( SELECT person, MAX(score) AS 'sc'
FROM tabgames GROUP BY person, date
) AS t1
GROUP BY person

http://dev.mysql.com/doc/refman/5.1/...ubqueries.html


XL