1

I need to select the five latest products of each brand category, then display those results grouped by the category.

MySQL database looks like;

Table product :

product_id (pk) 
product_name 
product_etc 
product_publish_date 
brand_id (fk)

Table brand 

brand_id (pk) 
brand_name

Results should come out like the following...

brand_a product k
brand_a product a
brand_a product h
brand_a product l
brand_a product 33

brand_b product h
brand_b product z
brand_b product 9
brand_b product ddd
brand_b product a

Tried with

Nonym
  • 6,199
  • 1
  • 25
  • 21
Adrian
  • 353
  • 2
  • 7
  • 20
  • SELECT * FROM product GROUP BY brand_id ORDER BY product_publish_date LIMIT 5 – Adrian Dec 15 '11 at 04:39
  • possible duplicate of [How to SELECT the newest four items per category?](http://stackoverflow.com/questions/1442527/how-to-select-the-newest-four-items-per-category) – Bill Karwin Dec 15 '11 at 04:40
  • what problem are you getting ? – gprathour Dec 15 '11 at 04:40
  • My query only returned 5 results (limit 5), I originally tried to do a bunch of queries like above but joining them / union but i must have had too many syntax errors and in the end I thought it would probably be a poor way (performance) to achieve my aim. – Adrian Dec 15 '11 at 04:42
  • I used this [solution](http://stackoverflow.com/questions/4666727/help-need-for-sql-query-select-latest-5-lists-of-each-category), there is 10 categories and 50 products per category. This wont be changing often so hopefully it will be enough, even if its not the most eloquent – Adrian Dec 15 '11 at 05:00

2 Answers2

2

try with:

SELECT p.*
FROM product p1
LEFT OUTER JOIN product p2
  ON (p1.brand_id = p2.brand_id AND p1.product_id < p2.product_id)
GROUP BY p1.item_id
HAVING COUNT(*) <= 5
ORDER BY brand_id, product_publish_date;

As bill karwin said, it's the same problem as in his link - my answer is just an adaptation of that answer to your case.

Tudor Constantin
  • 26,330
  • 7
  • 49
  • 72
1
select brand_name, product_name, count(*)
from brand
INNER JOIN product on brand.brand_id = product.brand_id
order by product_publish_date
group by brand_name, product_name
having count(*) <= 5
Zohaib
  • 7,026
  • 3
  • 26
  • 35