php - MYSQL - select all time high score -

assuming have table below

   id   |   name   |  userid  |  score  |      datestamp      |    ------------------------------------------------------------     1   |   john   |    1     |   44    |  2013-06-10 14:25:55      2   |   mary   |    2     |   59    |  2013-06-10 09:25:51      3   |   john   |    1     |   38    |  2013-06-10 21:25:15      4   |   elvis  |    3     |   19    |  2013-06-10 07:25:18     5   |   john   |    1     |   100   |  2013-06-14 07:25:18   

i want select time high-score of each user.

so example if player john have played ten rounds in 2013-06-10 , score 430 in total day. , in 2013-06-14 plays 16 rounds , scores 1220 in total day. want display the best score of user john, , on others players. in example above johns best score 2013-06-14 score 1220.

detailed example: if user john plays 3 rounds in 2013-06-10. first round scores 44, second time scores 38 , third time scores 55. total score day 137. , on next day 2013-06-11 plays 5 rounds total score of 220. in example best total score far 220. should group scores each day total. , compare total other days total , display highest total of user

thanks in advance

this should trick:

select userid, name, max(score) highscore (     select userid, name, sum(score) score, date(datestamp) scoredate     tablename     group userid, scoredate   ) dailyscores group userid 

the inner query fetches totals of each user's scores on each date (timestamp converted date remove time information), outer query gets highest total score of each date each user.

i took liberty of basing on j w's fiddle, , added row example data functionality obvious, try out here: http://www.sqlfiddle.com/#!2/f6bea/3