List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 22 2008 3:32pm
Subject:Re: GROUP & ORDER BY Question
View as plain text  
David,

What I am trying to limit this query to is the top 100 details ordered by
SUM(Volume) DESC for each unique LongDescription

For some solutions see 'Within-group quotas (Top N per group)' at 
http://www.artfulsoftware.com/infotree/queries.php

PB

-----

David Perron wrote:
> Hello MySQL Users-
>
> I am pretty sure this is a simple question and I am over thinking how to
> solve the problem, so I am hoping the community can help.
>
> I am selecting a pretty straightforward aggregation from a single stats
> table with the following format:
>
> SELECT
>     Description
>     LongDescription
>     Detail
>     SUM(Volume)
> FROM StatsTable
> GROUP BY Description
>     LongDescription
>     Detail
>
> What I am trying to limit this query to is the top 100 details ordered by
> SUM(Volume) DESC for each unique LongDescription
> This is what I am trying now but its not quite correct, it simply returns
> 100 of the top details.
>
> SELECT
>     Description
>     LongDescription
>     Detail
>     SUM(Volume)
> FROM StatsTable
> GROUP BY Description
>     LongDescription
>     Detail
> ORDER BY SUM(Volume) DESC
> LIMIT 100
>
> What I believe would work is a function in MySQL that is equivalent to the
> CUBE function in Oracle.
>
> Any direction would be greatly appreciated!
>
> David
>
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG. 
> Version: 7.5.524 / Virus Database: 269.24.0/1460 - Release Date: 5/22/2008 7:06 AM
>   

Thread
GROUP & ORDER BY QuestionDavid Perron22 May
  • Re: GROUP & ORDER BY QuestionRob Wultsch22 May
  • Re: GROUP & ORDER BY QuestionPeter Brawley22 May