MySQL - working out the AVG for a subset of MAX values

I have a table with columns 'Date, Name, Score'.

I wish to get the MAX(Score) for rows which share a common value (for e.g. the same date or even name), before averaging them to give me a figure, for example:

---- Date -----| -- Name -- | Score

2010-10-10 | John Smith | 86

2010-06-05 | Tedi Jones | 71

2010-10-10 | John Smith | 52

2010-06-05 | Tedi Jones | 68

2010-08-08 | Joe Bloggs | 79

2010-10-10 | John Sm开发者_Python百科ith | 46

So doing a MAX(Score) on the above would give me 86. However, what I'd like is the following:

MAX(Score) to give me the values 86 (MAX for date 10-10), 79 (MAX for date 08-08) and 71 (MAX for date 06-05) which I can then average to get 78.67. I'm hoping this is possible without having to resort to temp tables?

All replies are appreciated, thank you.

Total average of daily maximal values:

SELECT AVG(dailyMax) AS avgOfDailyMax
FROM (SELECT Date, MAX(Score) AS dailyMax FROM MyTable GROUP BY Date) as DailyMaxTable 

and daily maximal values:

SELECT Date, MAX(Score) AS dailyMax
FROM MyTable
GROUP BY Date

select Date, max(Score) as MaxScore
from MyTable
group by Date

If you want the Name as well, do

select m.Date, m.Name, m.Score
from (
    select Date, max(Score) as MaxScore
    from MyTable
    group by Date
) mm
inner join MyTable on mm.Date = m.Date 
    and mm.MaxScore = m.Score