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
