Why does a two-per-queue shortlist contain only one queue?

Prepare the report input

Original report takes at most two records from each queue, priority descending then id. Global LIMIT2 takes two overall. ROW_NUMBER within each queue enforces the supplied strict quota, including deterministic tie handling.

Every record and report rule below is original and fictional. Reproduce the setup in a fresh SQLite test database; input previews include every table in this small fixture.

CREATE TABLE items(id TEXT PRIMARY KEY,queue TEXT NOT NULL,priority INTEGER NOT NULL); INSERT INTO items VALUES('A','North',9),('B','North',8),('C','North',1),('D','South',7),('E','South',6),('F','South',5);

Compare misleading and corrected results

SELECT queue,id,priority FROM items ORDER BY priority DESC,id LIMIT 2;

WITH ranked AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY queue ORDER BY priority DESC,id) AS place FROM items) SELECT queue,id,priority FROM ranked WHERE place<=2 ORDER BY queue,priority DESC,id;

Bad output is North A/B only. Correct output also includes South D/E. The supplied tie rule keeps the quota at two by selecting the lower id at an equal priority.

Check a fresh boundary case

Make North C tie B at eight. The id rule retains B as the second slot, so North still has exactly two records.

Recreate the original setup in another fresh database, then apply the change and corrected query below. For an empty result, the table displays a labelled empty-result marker rather than a data record.

UPDATE items SET priority=8 WHERE id='C';
WITH ranked AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY queue ORDER BY priority DESC,id) AS place FROM items) SELECT queue,id,priority FROM ranked WHERE place<=2 ORDER BY queue,priority DESC,id;