Sort a Combined Operations Queue
UNION ALL score >= 95 rows as 'high_score' with NULL due_date rows as 'missing_due_date'. Return sort_id, label, and queue_source; finally sort by queue_source, sort_id.
- NULL handling
- Filtering
- Sorting
Exercise brief
Understand the request
Data pipeline operations lead An operations export merges exception streams.
Merge and sort two UNION ALL queues.
Return
- Use UNION ALL.
- Return sort_id, label, and queue_source.
- Finally sort by queue_source, then sort_id.
Constraints
- Use one final ORDER BY.
- Sort only combined output columns.
- Do not rely on branch order.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
sorting_cases
sort_idINTEGERlabelTEXTdue_dateDATEscoreDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
A set operation produces one combined result; only the outermost ORDER BY guarantees its presentation order.
Hint 2
Both SELECT branches must return compatible columns in the same positions. Give the literal source label the same alias in both branches.
Hint 3
Place ORDER BY queue_source, sort_id after the second SELECT, not inside either branch.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT sort_id, label, 'high_score' AS queue_source FROM sorting_cases WHERE score >= 95 UNION ALL SELECT sort_id, label, 'missing_due_date' AS queue_source FROM sorting_cases WHERE due_date IS NULL ORDER BY queue_source, sort_id;Why this works
UNION ALL preserves rows from both streams but does not promise their final presentation order. A single ORDER BY after the set expression sorts the combined output. Using the shared output alias and a unique sort_id tie-breaker makes the export portable and deterministic.
Success check
The four qualifying rows have one deterministic combined order.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| sort_id | label | queue_source |
|---|---|---|
| 2 | Alpha | high_score |
| 5 | gamma | high_score |
| 1 | alpha | missing_due_date |
| 3 | beta | missing_due_date |
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.