List:General Discussion« Previous MessageNext Message »
From:C.R.Vegelin Date:April 25 2006 1:32pm
Subject:Re: Top N selections + rest row
View as plain text  
Thanks Shawn,
According: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html:
When you use ROLLUP, you cannot also use an ORDER BY clause to sort the 
results. ...
Regards, Cor

----- Original Message ----- 
From: "Shawn Green" <sgreen_mysql@stripped>
To: "C.R.Vegelin" <cr.vegelin@stripped>; "Martijn Tonies" 
<m.tonies@stripped>; <mysql@stripped>; <Barry@stripped>
Sent: Tuesday, April 25, 2006 1:18 PM
Subject: Re: Top N selections + rest row


>
>
> --- "C.R.Vegelin" <cr.vegelin@stripped> wrote:
>
>> Thanks Martijn, Barry,
>> I was wondering whether it could be done in a single query.
>> I want users to decide how many countries they want,
>> and show world sales on top of report followed by the N countries.
>> This to enable relative country shares, both for reporting and
>> graphs.
>> For example, Top-10 countries + Rest in a pie graph.
>> So I need one additional row in the Top-N query.
>> Regards, Cor
>>
>> ----- Original Message ----- 
>> From: "Martijn Tonies" <m.tonies@stripped>
>> To: <mysql@stripped>
>> Sent: Tuesday, April 25, 2006 11:06 AM
>> Subject: Re: Top N selections + rest row
>>
>>
>> > Hi,
>> >
>> >>Anybody with smart ideas to get Top-N rows plus the rest row ?
>> >>Suppose I have ranked sales values descending for 2005, like:
>> >>Select Country, Sum(Sales) AS Sales From myTable
>> >>Where Year=2005 Group By Country
>> >>Order By Sales DESC LIMIT 25;
>> >>
>> >>Then I get Top-25 sales rows, but I also want a rest row,
>> >>where all 26 rows represent the world total.
>> >>I'm using MySQL 5.0.15.
>> >
>> > What about just removing the LIMIT clause?
>> >
>> > Or, alternatively, do a skip of the first 25 rows? (check the docs
>> > for that)
>> >
>> >
>> > Do you want to get this in a single result, or additional result?
>> >
>> > Martijn Tonies
>> > Database Workbench - development tool for MySQL, and more!
>> > Upscene Productions
>> > http://www.upscene.com
>> > My thoughts:
>> > http://blog.upscene.com/martijn/
>> > Database development questions? Check the forum!
>> > http://www.databasedevelopmentforum.com
>> >
>
> Have you considered using the WITH ROLLUP modifier?
>
> Select Country
>  , Sum(Sales) AS Sales
> From myTable
> Where Year=2005
> Group By Country WITH ROLLUP
> Order By Sales DESC
> LIMIT 25;
>
> http://dev.mysql.com/doc/refman/5.0/en/select.html
>
> Shawn Green
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> 


Thread
Top N selections + rest rowC.R.Vegelin25 Apr
  • Re: Top N selections + rest rowBarry25 Apr
  • Re: Top N selections + rest rowMartijn Tonies25 Apr
  • Re: Top N selections + rest rowC.R.Vegelin25 Apr
    • Re: Top N selections + rest rowShawn Green25 Apr
      • Re: Top N selections + rest rowMartijn Tonies25 Apr
      • Re: Top N selections + rest rowJoerg Bruehe25 Apr
      • Re: Top N selections + rest rowC.R.Vegelin25 Apr
      • Re: Top N selections + rest rowC.R.Vegelin25 Apr
        • Re: Top N selections + rest rowJoerg Bruehe25 Apr