Never rely on row order without ORDER BY
A “latest products” widget uses LIMIT 10 without ORDER BY and appears correct on a small database. After an index or query-plan change, it shows different records. SQL does not promise the desired order unless the query requests it.
State the order and its tie-breaker
SELECT id, name, created_at
FROM products
ORDER BY created_at DESC, id DESC
LIMIT 10;
The ID breaks ties between equal creation timestamps. If the product's business meaning of latest is publication time rather than insertion time, order by the relevant publication field instead.
Ordering and filtering belong together
A public widget must also exclude drafts, future records, and inaccessible tenants according to its purpose. A deterministic list of the wrong records is still wrong.
Choose an index based on the real filter and ordering combination, then inspect the plan. Avoid adding an index on creation time alone if every request first scopes by tenant and status.
Test more than ten records, multiple equal timestamps, and a future or hidden record. Assert the exact ordered IDs. Tests that only count ten results do not verify “latest.” When replacing offset pagination with cursors, keep the same deterministic ordering contract so users do not see surprising changes merely because the access strategy changed.