List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 23 2008 5:11am
Subject:Re: Is there a GROUP function that can help me with this?
View as plain text  
David,

 >My goal is to create a report, that lists the Top 100 most expensive 
BookNames, for every CategoryId in this table.

I think you can map the example under "Within-group quotas (Top N per 
group)" at http://www.artfulsoftware.com/infotree/queries.php to your 
requirement.

PB

David Perron wrote:
> Hi MySQL Users-
>
> I have a query problem I have been working on for quite some time and I am
> really at a loss to find a native function(s) to handle my task.
>
> I have this table:
>
> CREATE TABLE BookCategoryMetrics (
> BookName VARCHAR(255),
> CategoryId VARCHAR(128),
> RatingSum DOUBLE,
> Cost DOUBLE,
> PRIMARY KEY (BookName,CategoryId)
> );
>
> There is a 1:1 relationship between BookName and CategoryId.
> There are approximately 2 million unique values for BookName and 100 unique
> values for CategoryId.
>
> My goal is to create a report, that lists the Top 100 most expensive
> BookNames, for every CategoryId in this table.
> Obviously, I could write a wrapper script to loop through the CategoryId and
> pass them 1 at a time to this query to get the results, but this is
> obviously not the most efficient.
>
> SELECT
>     BookName,
>     CategoryId,
>     SUM(Cost) as TotalCost
> FROM BookCategoryMetrics
> WHERE CategoryId = 100
> GROUP BY BookName,CategoryId
> ORDER BY  TotalCost DESC
> LIMIT 100;
>
> Is there even a way to do this with straight MySQL, or is this a candidate
> for some kind of stored procedure?
>
> Thank you for any guidance!
>
> David
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.138 / Virus Database: 270.6.6/1623 - Release Date: 8/20/2008 8:12 AM
>
>
>   
Thread
Is there a GROUP function that can help me with this?David Perron23 Aug
  • Re: Is there a GROUP function that can help me with this?Peter Brawley23 Aug