Postgresql – Postgres: Given some parent id’s how can I SELECT X child records for each

postgresqlpostgresql-9.2

In Postgres, if I have :

Parent Table
id, name
1, Parent1
2, Parent2
.....
5, Parent5

Child Table
id, parent_id, name
1, 1, Child1
2, 1, Child2
.....
6, 2, Child6
.....
25, 5, Child25

so 5 children for each parent, how do I get, say, the first 3 children for parent id 1,3,4 in one query?

Best Answer

Assuming the "first" is defined through the id column in the child table, something like this should work:

select p.id, p.name, c.name
from parent p 
  join (
     select c.name, 
            c.parent_id, 
            row_number over (partition by c.parent_id order by c.id) as rn
     from child c
  ) ch
   on ch.parent_id = p.id 
  and ch.rn <= 3
where p.id in (1,3,4);
Related Question