>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
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.
> 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!
> 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