retrieve top 100 selling products in each category?

[xpblueScreenOfDeath]

I have table of categories, and a list of products with a counter in the product table. The counter in the product table gets incremented by 1 each time that product is sold. How do I get the top 100 selling products in each category?

 

[vacunita]

SELECT *FROM mytable ORDER BY counter_field DESC LIMIT 100;

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[r937]

vacunita, that might give the top 100 overall

xpblueEtCetera wants the top 100 products per category

xpblue, would you mind sharing the table layouts for your two tables?



r937.com | rudy.ca

 

[xpblueScreenOfDeath]

Category
---------
CategoryID
CategoryName


Product
---------
ProductID
CategoryID
ProductionName
Price
SellCount
...

 

[r937]

select c.CategoryName
     , p.ProductID
     , p.ProductionName
     , p.SellCount
  from Category as c
inner
  join Product as p
    on p.CategoryID = c.CategoryID
   and ( select count(*) 
           from Product  
          where CategoryID = p.CategoryID
            and SellCount  > p.SellCount ) < 100

r937.com | rudy.ca

 

[xpblueScreenOfDeath]

Thanks r937. With that query, it seems like it could return more than 100 records per category if there a products that have the same sell count. What would the performance of that query vs creating a store procedure that loops through the category table and select the top 100 products from each category and insert into a temporary table?

 

[r937]

yes, it would include ties, but only across the 100th place

as for performance, i think it would be better than looping with a temp table, but you should really test it both ways

please do let us know which is faster



r937.com | rudy.ca