Select rows with a clear order

The idea

This course uses SQLite SQL. A query describes the result you want rather than an instruction to visit rows in a particular physical order. SELECT chooses output expressions, FROM identifies the source, and WHERE retains rows whose condition is true. Name needed columns explicitly so changes to the source schema do not unexpectedly alter a report’s shape.

ORDER BY is the contract for result order; table storage or a previous query is not. Supply secondary keys when ties must be predictable, especially before LIMIT. AND and OR combine conditions, but parentheses make a business rule unmistakable. Begin with a tiny table whose rows include both included and excluded cases, then reason about filtering before projecting and sorting the remaining rows.

WITH tickets(id, priority, state) AS (VALUES (1,3,'open'),(2,5,'closed'),(3,3,'open'))
SELECT id, priority FROM tickets
WHERE state = 'open'
ORDER BY priority DESC, id ASC;

id | priority
1  | 3
3  | 3

The closed row is excluded. The remaining priorities tie, so the explicit ascending id key determines their order.