Put Missing Deadlines Last
Return sort_id, label, due_date, and priority from sorting_cases. Sort by due_date ascending with NULL deadlines last, priority descending within equal deadlines, and sort_id ascending as the final tie-breaker.
- NULL handling
- Filtering
- Sorting
Exercise brief
Understand the request
Operations queue manager A work queue should show dated cases first while retaining undated cases at the end.
List employees sorted by phone ascending, but force employees whose phone is NULL to appear LAST. Show first_name, last_name, phone.
Return
- Place every non-NULL due_date before NULL.
- Resolve equal deadlines by priority DESC and then sort_id ASC.
Constraints
- Control NULL placement explicitly.
- Use the engine-specific NULL-placement solution where required.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameTEXTlast_nameTEXTphoneTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
NULL defaults differ by engine, so make its rank explicit instead of trusting the default.
Hint 2
PostgreSQL, SQLite, and Oracle accept NULLS LAST. MySQL and SQL Server use CASE WHEN due_date IS NULL THEN 1 ELSE 0 END.
Hint 3
After the NULL rule, add due_date, priority DESC, and sort_id in that order.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT sort_id, label, due_date, priority FROM sorting_cases ORDER BY due_date NULLS LAST, priority DESC, sort_id;Why this works
NULL placement is dialect-sensitive. The explicit NULLS LAST form is concise where supported; a CASE null-rank expresses the same business rule in MySQL and SQL Server. The remaining keys make equal and missing dates deterministic.
Success check
Dated cases are chronological, missing dates are last, and all ties are deterministic.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| sort_id | label | due_date | priority |
|---|---|---|---|
| 4 | Beta | 2025-01-15 | 2 |
| 5 | gamma | 2025-01-15 | 1 |
| 2 | Alpha | 2025-02-01 | 1 |
| 6 | ALPHA | 2025-03-01 | 3 |
| 1 | alpha | NULL | 2 |
| 3 | beta | NULL | 1 |
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.