Postgres: Joining a parent table with its first child

24 April 2015

I end up using this a lot but not often enough to remember it. 
 I thought it might be useful to save it here instead of googling for it every single time.

This is the secret formula:

SELECT parents.*, children.* FROM parents JOIN ( SELECT C.*, row_number() OVER (PARTITION by C.parent_id ORDER BY C.created_at) AS rn FROM children C ) children ON children.parent_id = parents.id and children.rn = 1

Enjoy!


Originally published at underthehood.carwow.co.uk on April 24, 2015.

Interested in making an Impact? Join the carwow-team!
Feeling social? Connect with us on Twitter and LinkedIn :-)