Ordering, ties and NULL placement

Ordering is part of the result contract whenever users need a first item, ranking or pagination. The worked query considers known salaries, orders them highest first and breaks equal salaries by ascending role identity. That second key makes tied observations reproducible.

Roles 4 and 6 both have salary 120, so salary alone cannot decide their relative position. Adding id gives role 4 precedence. This does not assert that 4 is more suitable; it only defines a stable presentation rule for equal measured values.

ORDER BY accepts several expressions with independent directions. The engine compares salary first, then id only when salary ties. Filtering salary IS NOT NULL makes the missing-value population explicit rather than relying on an engine default for NULL placement.

The expected identity sequence is 4, 6, 1, 5, 2, 7, 8. Trace the two pairs of salary ties by hand. SQL engines can produce another tie order when you omit the identity key, especially after an index or plan change.

The counterexample selects one row after ordering only by salary. It may return 4 or 6, and a test that happens to observe 4 once does not establish a deterministic contract. A stable test needs a stated tie-breaker, not repeated execution until the expected row appears.

NULL ordering defaults also differ across engines. Specify NULLS FIRST or NULLS LAST where supported, or state a portable expression and test it. In this lesson the deliberate IS NOT NULL rule keeps the exercise focused on ties among known values.

List active roles by newest publication date and then highest identity first. Predict 8, 6, 4, 3, 2, 1. Publication dates repeat in this fixture, so a second ordering expression is required even though the dates look precise enough for a simple demonstration.

Use active status as the population rule and keep the chosen date and identity directions separate. You need no ranking window yet. Verify the complete sequence rather than checking that only the first row looks recent.

Step 1: Filter to active roles. Step 2: Sort date and identity in descending order.

The solution uses published DESC followed by id DESC. It returns six active records and keeps ties stable. The archived records 5 and 7 are absent because of the population filter, not because sorting or a limit silently removes them.

Acceptance checks should include at least two records with the same primary sort value. Otherwise an incomplete ORDER BY can appear correct. A later keyset pagination lesson uses the same pair of keys in its cursor predicate, keeping boundary selection consistent with ordering.

When explaining ordering, distinguish deterministic presentation from business priority. A primary-key tie-breaker resolves ambiguity but does not imply quality, relevance or fair ranking. Ask whether missing observations should be excluded, placed at the end or handled as a separate category.

Changing a tie policy can change a paginated result even when the underlying set of qualifying rows is unchanged. Document the policy with the API or report contract. The next lesson demonstrates why LIMIT without a total ordering is an unstable shortcut.

Optional stretch — after the core task works: Add explicit NULL placement to a salary ranking and compare both engines.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.