List:General Discussion« Previous MessageNext Message »
From:David Perron Date:August 23 2008 4:00am
Subject:Is there a GROUP function that can help me with this?
View as plain text  
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

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