SQL LIMIT: Select a Stable Top Set With an Explicit Tie Rule

The practice task

Select the two highest-priority fictional support tasks. Two tasks share the highest priority, so order those ties by the task ID to make the selected sequence reproducible.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE queue(id INTEGER, priority INTEGER);
INSERT INTO queue VALUES (1,7),(2,9),(3,9),(4,6);

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

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The descending priority puts both priority-nine tasks first. The ascending ID orders their tie as 2 then 3. LIMIT then keeps those two rows. Without ORDER BY, the two returned tasks would not constitute a defined highest-priority set.

The tie rule is a policy, not an accidental implementation detail. If earlier submission should win instead of smaller ID, include the submission time and a final unique key. State the intended choice so a later query change does not silently alter it.

LIMIT syntax varies across SQL products; some use TOP or FETCH forms. The example is explicitly SQLite. Regardless of spelling, a row cap and a ranking rule remain separate ideas, and neither implies that all ties at a boundary will be returned.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Do not call an unordered limit the top records.
• Make tie-breaking columns explicit.
• Check whether the task needs exactly N rows or every row tied at the boundary.