Why does a sorted shortlist omit the highest-priority record?

Prepare the report input

Original shortlist selects two highest numeric priorities, tie by id. The bad subquery first takes two lowest ids and only then sorts that truncated set. A correct outer sort cannot restore C once it was discarded.

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,priority INTEGER NOT NULL); INSERT INTO items VALUES('A',1),('B',2),('C',9);

Compare misleading and corrected results

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

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

Bad output B (priority2), A (priority1) is sorted within the wrong subset. Correct output C (priority9), B (priority2) comes from ordering all three before LIMIT.

Check a fresh boundary case

Add D at priority ten. Correct leaders become D at priority10 and C at priority9.

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.

INSERT INTO items VALUES('D',10);
SELECT id,priority FROM items ORDER BY priority DESC,id LIMIT 2;