CODEWITHSUNDEEP
mysqlgreatest-n-per-group
user892134
user892134

Reputation: 3224

LIMIT amount of rows fetched by JOIN

How do i LIMIT the child rows fetched to only 5?

Here is the SQLfiddle http://sqlfiddle.com/#!2/bd96a/2. Right now it fetches all rows with parentid='4' and parentid='14'. It should only fetch 5 of each parentid. Assuming i have hundreds of rows, it should only fetch a max of 5 for each parentid.

 "SELECT child.* FROM mytable as parent
     LEFT JOIN mytable as child on child.parentid=parent.id
     WHERE parent.pageid IN ( 1, 2) AND parent.submittype='1'
     ORDER BY child.id ASC";

How do i solve this?

Upvotes: 1

Views: 78

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

You want the greatest n per group.. This will do what you want. Sorry I'm on my phone so I can't format it.. If someone could edit it for me that would be great!

SELECT 
    child.*, 
    if(@a = child.parentid, @b :=@b+1, @b := 1) as counting_col, 
    @a := child.parentid
FROM mytable as parent
LEFT JOIN mytable as child on child.parentid=parent.id
CROSS JOIN(select @a :=0, @b:=1) t
WHERE parent.pageid IN ( 1, 2) 
  AND parent.submittype='1'
HAVING counting_col <=5
ORDER BY child.id ASC

Upvotes: 1

Related Questions