Top 3 Rows per Group

"The top three of each" is a question about groups, and GROUP BY cannot answer it, because it collapses the rows you want to keep. The pattern is to number the rows inside each group and keep the numbers up to three. This page does it with the shop's orders, shows what happens when two rows tie, and gives a second way that is faster on large tables.

Step 1: the order totals

CREATE VIEW order_totals AS
SELECT o.order_id, o.order_date, sum(i.quantity * p.price) AS total
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
JOIN products AS p ON p.product_id = i.product_id
GROUP BY o.order_id;

CREATE VIEW

Step 2: number the rows in each group and keep three

Here the groups are months. row_number() numbers the orders of each month from the largest total down. A window function cannot be used in WHERE, so the numbering goes in a subquery.

SELECT month, order_id, total, rn
FROM (
  SELECT date_trunc('month', order_date)::date AS month, order_id, total,
         row_number() OVER (
           PARTITION BY date_trunc('month', order_date)
           ORDER BY total DESC, order_id
         ) AS rn
  FROM order_totals
) AS ranked
WHERE rn <= 3
ORDER BY month, rn;

   month    | order_id | total | rn
------------+----------+-------+----
 2026-08-01 |        3 | 33.65 |  1
 2026-08-01 |        7 | 24.75 |  2
 2026-08-01 |        5 | 23.97 |  3
 2026-09-01 |       12 | 45.75 |  1
 2026-09-01 |       10 | 29.90 |  2
 2026-09-01 |       11 | 24.50 |  3
(6 rows)

Three orders for August and three for September. The order_id in the ORDER BY is the tie-breaker, so the numbering is the same on every run.

Step 3: what to do with ties

If two rows have the same value, the three functions disagree. Here is a small set with three rows tied for second place.

SELECT player, score,
       row_number() OVER (ORDER BY score DESC, player) AS row_number,
       rank()       OVER (ORDER BY score DESC)         AS rank,
       dense_rank() OVER (ORDER BY score DESC)         AS dense_rank
FROM (VALUES ('a', 10), ('b', 9), ('c', 9), ('d', 9), ('e', 5)) AS t(player, score)
ORDER BY row_number;

 player | score | row_number | rank | dense_rank
--------+-------+------------+------+------------
 a      |    10 |          1 |    1 |          1
 b      |     9 |          2 |    2 |          2
 c      |     9 |          3 |    2 |          2
 d      |     9 |          4 |    2 |          2
 e      |     5 |          5 |    5 |          3
(5 rows)

Filtering <= 3 gives three rows with row_number (a, b and c, and d is cut off only because of the tie-breaker), four rows with rank (a, b, c and d, all the ties) and all five rows with dense_rank, because it counts distinct scores and here there are only three. Pick the one that matches the question.

• "Exactly three rows per group": row_number, with a tie-breaker.
• "Everyone who is in the top three places, including ties": rank.
• "The top three distinct values": dense_rank.

Step 4: the LATERAL version

LATERAL lets a subquery refer to the row of the table before it. For each month it runs a small query with LIMIT 3. On a big table with an index, this can be much faster, because it stops after three rows for each group instead of numbering everything.

SELECT m.month, t.order_id, t.total
FROM (SELECT DISTINCT date_trunc('month', order_date)::date AS month FROM orders) AS m
CROSS JOIN LATERAL (
  SELECT ot.order_id, ot.total
  FROM order_totals AS ot
  WHERE date_trunc('month', ot.order_date)::date = m.month
  ORDER BY ot.total DESC, ot.order_id
  LIMIT 3
) AS t
ORDER BY m.month, t.total DESC;

   month    | order_id | total
------------+----------+-------
 2026-08-01 |        3 | 33.65
 2026-08-01 |        7 | 24.75
 2026-08-01 |        5 | 23.97
 2026-09-01 |       12 | 45.75
 2026-09-01 |       10 | 29.90
 2026-09-01 |       11 | 24.50
(6 rows)

The same six rows. LATERAL is standard SQL, and PostgreSQL has supported it for a long time. Not every database does. Use it when you have a short list of groups and a large table.

Related course: SQL Intermediate.