MySQL - how to retrieve columns in same row as the values returned by min/mx
- by Gala101
I couldn't frame the Question's title properly..
Suppose a table of weekly movie Earnings as below,
MovieName
MovieGross
WeekofYear
Year
So how do I get the names of top grossers for each week of this year
If I do
select MovieName , Max(MovieGross) , WeekofYear
from earnings where year = 2010 group by WeekofYear;
Then obviously query wont run,
select Max(MovieName) , Max(MovieGross) , WeekofYear
from earnings where year = 2010 group by WeekofYear;
would just give movies starting with lowest alphabet
Is using group-concat and then substring-index the only option here?
select
substring_index(group_concat(MovieName order by MovieGross desc),',',1),
Max(MovieGross) , WeekofYear from earnings where year = 2010
group by WeekofYear ;
Seems clumsy.. Is there any better way of acieveing this?
