From: Peter Brawley (peter.brawleyearthlink.net)
Date: Sat Aug 23 2008 - 00:11:40 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]