Limit number of rows per id in a SQL query

Question

I want to limit the results of 10 products for each brand_id. Here's how my query looks like:

SELECT 
products.id, products.brand_id
FROM products
  JOIN (
    select id, brand_id
    from products limit 10) 
    pinner ON products.id = pinner.id
WHERE pinner.brand_id IN ('1', '2','3') ;

That obviously does not work. Any suggestions?

EDIT: This post helped me with the solution: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

take away the subquery. Add an order by clause. Put the limit clause at the end. — Dan Bracuk, Feb 13 '14 at 17:14

score 3 · Accepted Answer · answered Feb 13 '14 at 17:16

Adapting from another answer

  set @brand_id= '';
  set @num = 0;

  select
  products.id, products.brand_id,
  @num := if(@brand_id= brand_id, @num + 1, 1) as dummy_1,
  @brand_id:= brand_id as dummy_2,
  @num as row_number
  from products
  where brand_id IN ('1', '2','3')
  group by
  brand_id,
  row_number
  having row_number <= 10;

score 2 · Answer 2 · answered Feb 13 '14 at 17:16

Try like this

SET @rownum =0;

SELECT id,brand_id FROM
(
    SELECT 
    products.id, products.brand_id, @rownum := @rownum + 1 AS rank
    FROM products
      JOIN (
        SELECT id, brand_id
        from products limit 10) 
        pinner ON products.id = pinner.id
    WHERE pinner.brand_id IN ('1', '2','3') 
) AS T
Where rank < 10

Limit number of rows per id in a SQL query

2 Answers2

Linked