CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
SAMPro
SAMPro

Reputation: 1099

SELECT n row FROM table1 PER table2


I have 2 table: Cats(category) and post.
I want to SELECT n post per each category.

I have tried this:

SELECT * FROM cat
RIGHT JOIN (SELECT * FROM post WHERE post.CatID=cat.ID LIMIT 3 ) ...

The problem is MySQL does not recognize cat.ID inside sub query.

Regards

Upvotes: 1

Views: 157

Answers (1)

John Woo
John Woo

Reputation: 263723

SELECT  a.ID, 
        a.Category,
        b.Description
FROM    Category a
        INNER JOIN Post b
            ON a.ID = b.Cat_ID
WHERE   
(
    SELECT  COUNT(*)
    FROM    Post c
    WHERE   b.Cat_ID = c.Cat_ID AND
            b.ID <= c.ID
) <= 2

Upvotes: 1

Related Questions