MAX(id) variation for finding the latest row and why it can break

max-id-variation.sql

-- A common variation uses MAX(id) to find the latest status:
SELECT u.id, s.status
FROM users u
JOIN user_statuses s ON s.user_id = u.id
WHERE s.id = (
    SELECT MAX(id) FROM user_statuses s2 WHERE s2.user_id = u.id
);

-- This assumes the highest id is always the latest status.
-- That holds under normal operation, but breaks during backfills
-- or data corrections where older statuses may be inserted with
-- higher IDs. ORDER BY created_at DESC, id DESC expresses the
-- business rule directly and doesn't depend on insertion order.