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