From: Peter Brawley
Date: August 23 2008 5:11am
Subject: Re: Is there a GROUP function that can help me with this?
List-Archive: http://lists.mysql.com/mysql/214213
Message-Id: <48AF9C0C.5010200@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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