postgres, how to select where-in and limit N per foreign key?-CodePudding

In my postgres db, I have a posts and comments table:

posts:

id  title
1   post1
2   post2
3   post3

comments:

id  post_id  content
1   1        a   
2   1        b
3   1        c
4   2        d
5   3        e
6   3        f
7   3        g

How do I select comments where post_id in (1,3), but limit to 2 per post_id, so that I get:

id  post_id  content
1   1        a   
2   1        b
5   3        e
6   3        f

CodePudding user response：

Use ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY content) rn
    FROM comments
    WHERE post_id IN (1, 3)
)

SELECT id, post_id, content
FROM cte
WHERE rn <= 2;

CodePudding user response：

You can use a lateral join.

select foo.* from 
posts 
cross join lateral 
(select * from comments where post_id=posts.id order by content limit 2) foo 
where post_id IN (1, 3);

In your question, it is not clear what to order by, so I just copied the order from Tim's answer. This has the potential to be much faster than the row_number(), as that will number all comments for each relevant post then filter out ones with high numbers. While the lateral just counts to 2 for each post, then stops.