Return a Stable Top Three
Return sort_id, label, and score from sorting_cases for the three highest-scoring cases, ordered by score descending and sort_id ascending.
- Sorting
- Top-N
Exercise brief
Understand the request
Assessment operations manager A shortlist must contain exactly three highest-scoring cases, including a deterministic cutoff when scores tie.
List the top 5 highest-paid employees. Use a deterministic tie-breaker (employee_id ascending) so the result is stable. Show first_name, last_name, salary, employee_id.
Return
- Return exactly three rows.
- Use sort_id to resolve score ties, including the tie at the cutoff.
Constraints
- Order before applying the row limit.
- Use the engine-specific top-N syntax.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameTEXTlast_nameTEXTsalaryDECIMALemployee_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
ORDER BY defines which rows qualify before the row limit is applied.
Hint 2
score DESC ranks highest first; sort_id makes equal scores deterministic.
Hint 3
SQLite/MySQL use LIMIT, PostgreSQL/Oracle can use FETCH FIRST, and SQL Server can use TOP.
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 LIMIT 3;Why this works
Top-N is only reproducible when ORDER BY defines a total order. The fixture has equal scores at the cutoff, so omitting sort_id can select a different third row. Row-limiting syntax differs, but the business ordering contract is the same.
Success check
The same three rows are returned in the same order on every execution.
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 |
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.