Skip to content
GreenSnippets

Top N rows per group

Get the latest or highest N rows for each category using ROW_NUMBER in a subquery.

SQL
SELECT *
FROM (
  SELECT
    p.*,
    ROW_NUMBER() OVER (
      PARTITION BY p.category_id
      ORDER BY p.created_at DESC, p.id DESC
    ) AS rn
  FROM products AS p
) AS ranked
WHERE rn <= 3
ORDER BY category_id, rn;

Example / expected output

Output
-- The 3 newest products in every category

How it works

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) numbers the rows inside each group, and an outer filter keeps the first N. Use RANK instead if ties should all be kept. For small N and an indexed group column, LATERAL can be faster (see the PostgreSQL docs), but this form works on every major database.

This snippet has been reviewed but was not executed by an automated test. Test it in your own environment before relying on it.

Related snippets