On Wed, May 21, 2008 at 9:45 PM, David Perron <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
