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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
