List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:May 22 2008 6:22am
Subject:Re: GROUP & ORDER BY Question
View as plain text  
On Wed, May 21, 2008 at 9:45 PM, David Perron <david.perron@stripped> 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
>

Perhaps you are wanting something like:
SELECT
   Description
   LongDescription
   Detail
   SUM(Volume)
FROM (SELECT * FROM StatsTable ORDER BY Detail DESC LIMIT 0,100)
StatsTable
GROUP BY Description,
   LongDescription,
   Detail
ORDER BY SUM(Volume) DESC


Basically saying something like, give me the records in StatsTable
that have the 100 largest `Detail`. Take this result and group by
Description,   LongDescription,   Detail and then order by aggregating
function SUM(Volume) DESC.

I am not sure about what you are asking and might be giving you bad
info. Sending ddl and ideal results of the query is slight more
ideal....

-- 
Rob Wultsch
wultsch@stripped
wultsch (aim)
Thread
GROUP & ORDER BY QuestionDavid Perron22 May
  • Re: GROUP & ORDER BY QuestionRob Wultsch22 May
  • Re: GROUP & ORDER BY QuestionPeter Brawley22 May