1

I have a table named Category like

Id    Name
1     A
2     B

And i have product table like

Id   Name    Category_id
1    C       1
2    D       1
3    E       1
4    F       2
5    G       2
6    H       2

I want to select 2 items from each category. I am applying this query

Select Product.id,Product.name as pname,category.name as cname from product join category where Category_id=Category.id limit 0,4

But it's returning first four data of the product table like

Product.id    Pname    Cname
1             C        A
2             D        A
3             E        A
4             F        B

But i Want to get 2 product from each category

Product.id    Pname    Cname
1             C        A
2             D        A
4             F        B
5             G        B
ADyson
  • 57,178
  • 14
  • 51
  • 63
Rohit Sankhala
  • 19
  • 8
  • doesn't seem to have anything to do with PHP. I edited your tags. If you use accurate tags which relate more closely to your specific issue (rather than to, say, technology which you use elsewhere in your app but isn't relevant to this problem) then it's more likely that people with relevant skills will see your question and be able to help. – ADyson Jan 06 '20 at 10:09

1 Answers1

1

If you are running MySQL 8.0, you can use row_number():

select p.id pid, name, p.name pname, c.name cname
from (
    select p.*, row_number() over(partition by category_id order by id) rn from product p
) p
inner join category c on c.id = p.category_id
where p.rn <= 2
GMB
  • 216,147
  • 25
  • 84
  • 135