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
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;
Just for interest and following up on the answer by Strawberry (which probably covers your requirements), the following should give you consistently a single matching id / name if 2 people both share the same highest score for a month (I have just randomly chosen to get the details for the user with the highest id).
SELECT winner.id, winner.score, DATE_FORMAT(`date`,'%Y%m' ) AS `month`
FROM
(
SELECT DATE_FORMAT(`date`,'%Y%m' ) AS aMonth, MAX(score) AS max_score
FROM winner
GROUP BY aMonth
) Sub1
INNER JOIN
(
SELECT DATE_FORMAT(`date`,'%Y%m' ) AS aMonth, score, MAX(id) AS max_id
FROM winner
GROUP BY aMonth, score
) Sub2
ON Sub2.score = Sub1.max_score
AND Sub2.aMonth = Sub1.aMonth
INNER JOIN winner
ON winner.score = Sub1.max_score
AND DATE_FORMAT(winner.`date`,'%Y%m' ) = Sub1.aMonth
AND winner.id = Sub2.max_id