想要显示每月分组的最高得分者详细信息

Question

i have a table of data DB table look like

   id   |   name   |  userid  |  score   |      date      |
   ------------------------------------------------------------
    1   |   john   |    4     |   233    |  2014-02-02 
    2   |   mary   |    5     |  1256    |  2013-02-05  
    3   |   john   |    6     |   100    |  2013-03-08 
    4   |   elvis  |    7     |   123    |  2013-03-04 
    5   |   john   |    2     |   1234   |  2013-03-02

now i want to show one highest scorer details of every month.my retrieve data output will be json format

currently my query output show

"monthly_winners":[
    {
        "id":"1",
        "score":"233",
        "month":"Feb"
    },
    {
        "id":"3",
        "score":"100",
        "month":"Mar"
    }
],

But It will be

"monthly_winners":[
    {
        "id":"2",
        "score":"1256",
        "month":"Feb"
    },
    {
        "id":"5",
        "score":"1234",
        "month":"Mar"
    }
],

i cant understand whats wrong in my query

my query is

SELECT id,score, DATE_FORMAT(`date`,'%b' ) AS month FROM `winner`  GROUP BY month ORDER BY score DESC

Answer 1

SELECT x.*
  FROM my_table x
  JOIN 
     ( SELECT DATE_FORMAT(date,'%Y%m')yearmonth
            , MAX(score) max_score 
         FROM my_table 
        GROUP 
           BY DATE_FORMAT(date,'%Y%m')
     ) y
    ON y.yearmonth = DATE_FORMAT(x.date,'%Y%m')
   AND y.max_score = x.score;