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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

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

Your Answer


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.