Make a Sort Deterministic
Return sort_id, label, and score from sorting_cases, ordered by score descending and then by the unique sort_id.
- Sorting
Exercise brief
Understand the request
Data platform reviewer A downstream assessment export compares files byte for byte, so equal scores need a stable order.
Sort employees by job_id ascending. To make the result reproducible (rather than engine-dependent for ties), add employee_id ascending as a tie-breaker. Show first_name, last_name, job_id, employee_id.
Return
- Rank higher scores first.
- Resolve every score tie with sort_id ascending.
Constraints
- Use sort_id as the final deterministic tie-breaker.
- Do not rely on insertion order.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameTEXTlast_nameTEXTjob_idTEXTemployee_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Rows with equal ORDER BY values have no guaranteed relative order.
Hint 2
score DESC defines the ranking; a unique sort_id defines the order inside each score tie.
Hint 3
ORDER BY score DESC, sort_id ASC
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT sort_id, label, score FROM sorting_cases ORDER BY score DESC, sort_id;Why this works
ORDER BY score DESC alone is only a partial order because three rows share score 91. Adding the unique sort_id creates a total order that stays stable across plans, engines, and repeated executions.
Success check
Repeated executions return the same row sequence for every score group.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| sort_id | label | score |
|---|---|---|
| 2 | Alpha | 95 |
| 5 | gamma | 95 |
| 1 | alpha | 91 |
| 3 | beta | 91 |
| 6 | ALPHA | 91 |
| 4 | Beta | 88 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
LIMIT & OFFSET
Practice deterministic top-N, cutoff ties, offset pagination, composite keyset cursors, and resumable bounded batches.
Ranking & NTH Value
Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
Open the interactive workspace and practice across SQL topics.