Two per Shelf

Numbering rows

row_number() is a window function that gives each row its position in the window. It needs an ORDER BY inside the OVER brackets, because "position" is meaningless without one.

CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, apiary TEXT, jars INTEGER);
INSERT INTO hives (label, apiary, jars) VALUES
  ('north hedge', 'home',    14),
  ('orchard',     'home',    31),
  ('lane end',    'roadside', 6),
  ('churchyard',  'roadside', 22),
  ('back field',  'home',     9);

SELECT
  label,
  apiary,
  jars,
  row_number() OVER (ORDER BY jars DESC, label) AS place
FROM hives
ORDER BY place;

label       | apiary   | jars | place
------------+----------+------+------
orchard     | home     |   31 |     1
churchyard  | roadside |   22 |     2
north hedge | home     |   14 |     3
back field  | home     |    9 |     4
lane end    | roadside |    6 |     5
(5 rows)

One to five, straight down the table. The , label after jars DESC is a tie-breaker: without it, two hives with the same count would be numbered in an order the database chooses and may choose differently next time.

PARTITION BY restarts the count

PARTITION BY splits the rows into groups and gives each row a window containing only its own group. It is the window equivalent of GROUP BY — with the crucial difference that nothing is collapsed.

CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, apiary TEXT, jars INTEGER);
INSERT INTO hives (label, apiary, jars) VALUES
  ('north hedge', 'home',    14),
  ('orchard',     'home',    31),
  ('lane end',    'roadside', 6),
  ('churchyard',  'roadside', 22),
  ('back field',  'home',     9);

SELECT
  apiary,
  label,
  jars,
  row_number() OVER (PARTITION BY apiary ORDER BY jars DESC, label) AS place_in_apiary,
  count(*)     OVER (PARTITION BY apiary)                           AS hives_in_apiary
FROM hives
ORDER BY apiary, place_in_apiary;

apiary   | label       | jars | place_in_apiary | hives_in_apiary
---------+-------------+------+-----------------+----------------
home     | orchard     |   31 |               1 |               3
home     | north hedge |   14 |               2 |               3
home     | back field  |    9 |               3 |               3
roadside | churchyard  |   22 |               1 |               2
roadside | lane end    |    6 |               2 |               2
(5 rows)

The numbering now runs 1, 2, 3 within home and 1, 2 within roadside. Inside the brackets the order of the parts is fixed: PARTITION BY first, then ORDER BY, then a frame clause if there is one.

Why the filter needs a CTE

Now the obvious next step: keep the rows where place_in_apiary <= 2. You cannot do it in the same query.

CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, apiary TEXT, jars INTEGER);
INSERT INTO hives (label, apiary, jars) VALUES ('north hedge', 'home', 14), ('orchard', 'home', 31);

SELECT label, apiary
FROM hives
WHERE row_number() OVER (PARTITION BY apiary ORDER BY jars DESC) <= 2;

Error in the statement on line 4: misuse of window function row_number()

WHERE is evaluated before the window functions are, so there is no row number yet to compare. The fix is to compute the numbering in one step and filter in the next, which a CTE makes readable:

CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, apiary TEXT, jars INTEGER);
INSERT INTO hives (label, apiary, jars) VALUES
  ('north hedge', 'home',    14),
  ('orchard',     'home',    31),
  ('lane end',    'roadside', 6),
  ('churchyard',  'roadside', 22),
  ('back field',  'home',     9);

WITH ranked AS (
  SELECT
    apiary,
    label,
    jars,
    row_number() OVER (PARTITION BY apiary ORDER BY jars DESC, label) AS place
  FROM hives
)
SELECT apiary, place, label, jars
FROM ranked
WHERE place <= 2
ORDER BY apiary, place;

apiary   | place | label       | jars
---------+-------+-------------+-----
home     |     1 | orchard     |   31
home     |     2 | north hedge |   14
roadside |     1 | churchyard  |   22
roadside |     2 | lane end    |    6
(4 rows)

Four rows: the best two from each apiary. That is the whole top-N-per-group pattern, and it is worth being able to write from memory.

row_number or rank?

They differ only on ties, and the difference decides how many rows you get back.

• row_number() never repeats a number, so <= 2 always gives at most two rows per group — but when two rows tie, which of them is second is decided by your tie-breaker, or arbitrarily if you did not supply one.
• rank() gives tied rows the same number and then skips, so <= 2 can return three or more rows per group. That is right for a prize list and wrong for "show me two rows".

Pick deliberately. For a leaderboard people will read, rank() is usually honest; for a fixed-size list, row_number() with a real tie-breaker.

Your turn

The editor holds eight tools across four shelves and a query that already has the right shape: a CTE that numbers the rows, and an outer query that keeps the low numbers.

Press Run first. Two rows come back, both from the whole-library top two, because the numbering runs straight down the table and ignores the shelves.

Add one clause inside the OVER (...) brackets so the numbering restarts for each shelf. Run it again and six rows should come back: two for garden, one for hand, one for plumbing and two for power.

Then change WHERE place <= 2 to <= 1 to get the dearest tool on every shelf — a query you will want surprisingly often.

If something goes wrong

If you still get two rows, the new clause is probably outside the OVER brackets, where it does nothing to the window.

If you get a syntax error near PARTITION, it is after the window's ORDER BY. Inside the brackets, PARTITION BY comes first.

If you get eight rows, the WHERE place <= 2 has gone missing from the outer query.

If you get misuse of window function row_number(), the window function has been moved into the WHERE clause. It has to stay inside the CTE.

Nothing here can break. The table is rebuilt from scratch on every run.