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?