CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Martin
Martin

Reputation: 367

Greatest 'n' per group by month

I have a mysql table with date, name and rating of a person. I need to build a query to show the best person of each month. The query above gives me maximum rating of the month but wrong name/id of person.

SELECT DATE_FORMAT(date,'%m.%Y') as date2, MAX(rating), name FROM test GROUP BY date2

Here's sqlfiddle with sample table: http://sqlfiddle.com/#!2/4dd54b/9

I read several greatest-n-per-group topics, but those queries didn't work, I suppose it's because of grouping by DATE_FORMAT. So here I ask.

Upvotes: 0

Views: 265

Answers (2)

Strawberry
Strawberry

Reputation: 33945

A faster solution might look like this - although removal of the DATE_FORMAT function altogether will speed things up even further...

SELECT x.* 
  FROM test x
  JOIN
     ( SELECT DATE_FORMAT(date,'%Y-%m') dt
            , MAX(rating) max_rating 
         FROM test 
        GROUP
           BY DATE_FORMAT(date,'%Y-%m')
     ) y
    ON y.dt = DATE_FORMAT(x.date,'%Y-%m')
   AND y.max_rating = x.rating;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270281

The easiest way is to use the substring_index()/group_concat() trick:

  SELECT DATE_FORMAT(date, '%m.%Y') as date2, MAX(rating),
         substring_index(group_concat(name order by rating desc), ',', 1) as name
  FROM test
  GROUP BY date2;

Upvotes: 2

Related Questions