Skip to content

.tip

· one of the small things

LATERAL for the top N per parent

"The three latest posts for each author" is a join where the right side depends on the row on the left, and that's what LATERAL is for.

SELECT a.name, p.title
FROM authors a
CROSS JOIN LATERAL (
  SELECT title FROM posts WHERE author_id = a.id
  ORDER BY created_at DESC LIMIT 3
) p;

The subquery runs once per author and uses that author's index. Without LATERAL you'd rank every post in the table just to keep three per author.

postgresql