Top-K Per Group (TOP … PER)

Syntax

TOP <count> <sort-specs> [PER <keys>] (Relation)
TOP <offset>, <count> <sort-specs> [PER <keys>] (Relation)
TOP <count> WITH TIES <sort-specs> [PER <keys>] (Relation)   -- also keeps every row tied with the last one kept
-- returns the kept input rows, schema unchanged

TOP 3 amount DESC PER customer_id (Orders)
TOP 5, 3 amount DESC PER customer_id (Orders)   -- skip 5, take 3 per group
TOP 3 amount DESC (Orders)                      -- one global group: the top 3 overall
TOP 2 WITH TIES seconds PER age_group (Results)   -- a tie shares a place: two tied for first fill both places, and nobody is second

Description

TOP keeps the highest (or lowest) N rows within each group. It answers "the 3 biggest orders for each customer", "the 5 most recent events per device", "the top earner in each department" — the per-group ranking that SQL needs a window function for. You give the number to keep, how to rank (a sort spec), and the column(s) that define each group (PER).

TOP keeps the first rows in the order the sort spec gives, and a sort key with no direction is ascending, as in τ. So TOP 1 amount keeps the lowest amount: write amount DESC for the highest. A first sort key written with neither ASC nor DESC draws a warning saying so (see Notes).

Technical Description

PER is optional. Written without it, there is one global group and TOP is the top count rows overall — which is what λ over τ means, and what the optimizer rewrites that pair into (LIM-003). Writing it directly says the same thing and costs the reader nothing to recognise.

TOP returns, per partition defined by the PER keys, the first count rows in the order of the sort specifications (optionally skipping offset rows first) — the ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) ≤ k idiom. Requires ≥1 sort spec. Output = full input rows (a windowed filter). It cannot desugar (no per-group ranking primitive) and does not push down ([bag]).

WITH TIES keeps, in each group, every further row that ties with the last one kept on all the sort keys — SQL's FETCH FIRST n ROWS WITH TIES and TOP n WITH TIES. A tie is equality under the sort, so two NULLs tie. A global TOP n WITH TIES folds into SQL on PostgreSQL; with PER, or on another backend, it runs in the engine.

Examples

The 3 biggest orders per customer:

TOP 3 amount DESC PER customer_id (Orders)

The most recent reading per sensor:

TOP 1 reading_time DESC PER sensor_id (Readings)

2nd and 3rd place per category (skip the winner):

TOP 1, 2 score DESC PER category (Entries)

Top earner in each department:

TOP 1 salary DESC PER dept (Employees)

Worked Example

A retailer wants the two biggest orders for each customer — a leaderboard per group, keeping the full order rows (not just the amount).

Orders := [
| customer | order_id | amount |
|----------|----------|--------|
| Alice    | 1        | 120    |
| Alice    | 2        | 80     |
| Alice    | 3        | 200    |
| Bob      | 4        | 50     |
| Bob      | 5        | 75     |
];

Biggest := { TOP 2 amount DESC PER customer (Orders) };

Within each customer partition the rows are ranked by amount DESC and the top two kept:

query { Biggest };

 customer  order_id  amount
 ────────  ────────  ──────
 Alice            3     200
 Alice            1     120
 Bob              5      75
 Bob              4      50
(4 rows)

Rename  ~4 rows
└─ TOP 2 PER customer  ~4 rows
   └─ Scan Orders  ~5 rows

Bob has only two orders, so both survive. To get 2nd and 3rd place per customer (skip the winner) use the offset form — TOP 1, 2 amount DESC PER customer (Orders) skips 1 row and takes the next 2. And to keep only a single value per group rather than the whole row — e.g. just the highest amount — reach for the ARGMAX aggregate instead.

When two rows tie for the last place kept, plain TOP keeps the one that arrived first. WITH TIES keeps both:

Results := [
| race | runner | secs |
|------|--------|------|
| r1   | Ann    | 61   |
| r1   | Ben    | 59   |
| r1   | Cat    | 59   |
| r2   | Dan    | 70   |
| r2   | Eve    | 72   |
];

query { TOP 1 WITH TIES secs ASC PER race (Results) };

 race  runner  secs
 ────  ──────  ────
 r1    Ben       59
 r1    Cat       59
 r2    Dan       70
(3 rows)

TOP 1 WITH TIES PER race  ~2 rows
└─ Scan Results  ~5 rows

Limitations

Requires at least one sort spec: without one, "highest" has no meaning.

query { TOP 3 (Orders) };

Returns whole rows, not a single aggregated value.

The count and the offset are whole numbers; a fraction is a parse error:

query { TOP 2.5 amount DESC PER customer_id (Orders) };

Alternatives

γ with ARGMAX/ARGMIN returns one value from the extreme row per group (not the whole row, and only one). λ over τ for a global top-N without grouping, which the optimizer rewrites into a keyless TOP (LIM-003, see the optimizer page).

See Also

log source — TOP over a web server's access log, among other windowing examples on one real dataset

argmax, argmin, group, sort, limit

Notes

TOP is the "keep the actual rows" counterpart to the ARGMAX aggregate (which returns a value).

Direction. "Top" reads as the highest, as SQL's SELECT TOP does, but a TOP ranks in its sort keys' order, and a key with no direction is ascending. So when the first sort key has neither ASC nor DESC, validation gives a warning:

warning: Top-k TOP: story_points has no direction, so TOP keeps the rows with the lowest story_points: write story_points DESC for the highest, or story_points ASC to keep the lowest without this warning

Writing either direction silences it, so TOP 1 secs ASC PER race keeps each race's fastest time quietly. Only the first key decides which rows lead; a later key breaks ties among them and is quiet without a direction. τ and the sorts inside windows say nothing, since "sort by x" carries no "highest".

Ties. Where the sort specifications cannot separate two rows, the one that reached the operator first is kept — the same rows a full sort followed by a limit would leave. So TOP 1 amount DESC PER customer over two orders of equal amount returns the earlier of the two rather than an arbitrary one. A relation has no inherent row order, though, so "earlier" is only as meaningful as the order the input delivers: add a tie-breaking sort key when the choice matters, or write WITH TIES to keep every row tied for the last place.

Partition pruning (TOPK-001). A selection that fixes a PER key to a constant above a TOP — σ customer = "Alice" (TOP 2 amount DESC PER customer (Orders)) — is pushed below the operator by the optimizer, so top-k is computed only for the matching group instead of every group: TOP 2 amount DESC PER customer (σ customer = "Alice" (Orders)). This is safe because top-k is computed independently per group. Only equalities on a PER key are pushed; a predicate on any other column (or on a computed rank) stays as a residual σ above the operator. Look for a TOPK-001 record in relix optimize --report output.