0

I need to select n of data for each grouped brand id

with the like condition i can select a product and its brands but i need to get group the results and need to select 3 data from each grouped id ,any possible ways ? with single query

I have tried with limit 

SELECT prod.products_id,prod.products_name,prod.products_code,ae_products_defaultimg,brands.brands_name,brands.brands_id
FROM `productsTab` as prod
LEFT JOIN brandsTab as brands ON prod.brands_id=brands.brands_id
WHERE prod.`products_name` LIKE '%black%' 
GROUP BY brands.brands_id
LIMIT 6

Product table 

id  product_name    brand_d
----    ------------    -------
0   black shoe          1
1   black shoe          1
2   white shoe          2
3   white shoe          2
4   black shoe          3
5   red shoe            4
6   black shoe          4
6   pink shoe           3
6   black shoe          3
6   yellow shoe         3

brand table

id  brand_name
----    ------------    
0   brand A          
1   brand B         
2   brand C          
3   brand D   
4   brand E

Expected results 

brand_id  brand_name    product_name
----    ------------    ---------------
1   brand B          black shoe
1   brand B          black shoe
3   brand D          black shoe
3   brand D          black shoe
4   brand E          black shoe
GMB
  • 216,147
  • 25
  • 84
  • 135
Sukesh
  • 184
  • 2
  • 17
  • This is a very common problem. I have added [tag:greatest-n-per-group] tag to the question. You can get a plethora of techniques here: https://stackoverflow.com/questions/tagged/greatest-n-per-group?tab=Votes – Madhur Bhaiya Oct 03 '19 at 09:38

1 Answers1

2

In MySQL 8.0, use ROW_NUMBER():

SELECT *
FROM (
    SELECT
        b.id brand_id,
        b.brand_name,
        p.product_name,
        ROW_NUMBER() OVER(PARTITION BY b.id ORDER BY p.id) rn
    FROM brand b
    INNER JOIN product p ON p.brand_id = b.id
    WHERE p.products_name LIKE '%black%' 
) x
WHERE rn <= 3
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
GMB
  • 216,147
  • 25
  • 84
  • 135