How to get an product limit in a category using php mysql?

Question

I have a sample data:

products (id, name, category_id)
(1, 'Test 1', 1)
(2, 'Test 2', 1)
(3, 'Test 3', 1)
(4, 'Test 4', 2)
(5, 'Test 5', 2)
(6, 'Test 6', 2)
(7, 'Test 7', 3)

How to ideas, get 2 product in 1 category_id ex:

(1, 'Test 1', 1)
(2, 'Test 2', 1)
(4, 'Test 4', 2)
(5, 'Test 5', 2)
(7, 'Test 7', 3)

This is just a slight variation on one of the most frequently repeated duplicated question on all of SO - "greatest n per group". Please search for similar questions, have a go yourself and then show what you've tried. — Mark Byers, Sep 28 '12 at 08:20

score 2 · Accepted Answer · answered Sep 28 '12 at 08:58

2

Try this,

SELECT id, name, category_id
FROM   products p
WHERE 
(
   SELECT count(*) 
   FROM   products f
   WHERE  f.category_id = p.category_id AND
          f.id <= p.id
) <= 2;

SQLFiddle Demo

answered Sep 28 '12 at 08:58

John Woo

258,903
69
498
492

How to get an product limit in a category using php mysql?

1 Answers1

SQLFiddle Demo