Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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);
share|improve this answer
    
Great, thanks @a_horse_with_no_name. Tiny changes ad it worked perfectly : ` select p.id, p.name, c.name from parent p join ( select ch.name, ch.parent_id, row_number() over (partition by ch.parent_id order by ch.id) as rn from child ch ) c on c.parent_id = p.id and c.rn <= 3 where p.id in (1,3,4); ` –  Mark Selby Apr 21 '13 at 11:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.