Include Every Row Tied at the Cutoff
Return the first three events by severity with all cutoff ties, then display the selected rows by severity descending and event_id ascending.
- Window functions
- Subqueries
- Filtering
- Sorting
Exercise brief
Understand the request
Incident response product owner An escalation report must include the three highest-severity positions without arbitrarily excluding events tied with the third position.
Return event_id, stream, and severity for the first three severity positions, including every event tied at the third-row cutoff. Display severity descending and event_id ascending.
Return
- Return event_id, stream, and severity.
- Return every event tied at the third-row severity boundary.
Constraints
- Use the engine row-limiting form that includes ties.
- Do not add event_id to the inner cutoff order because that would make every position unique.
- Use an outer ORDER BY for deterministic display within the selected severity peers.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
page_events
event_idINTEGERevent_timeTIMESTAMPstreamTEXTseverityINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Exactly N rows and N positions with ties are different business contracts.
Hint 2
The cutoff must be ordered only by severity so equal severities remain peers.
Hint 3
Apply FETCH FIRST 3 ROWS WITH TIES in the inner query, then order the selected rows by severity and event_id outside.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH ranked AS (SELECT event_id, stream, severity, DENSE_RANK() OVER (ORDER BY severity DESC) AS severity_rank FROM page_events) SELECT event_id, stream, severity FROM ranked WHERE severity_rank <= 2 ORDER BY severity DESC, event_id;Why this works
WITH TIES expands the limited result to every row equal on the cutoff ORDER BY values. Keeping the unique event_id out of the cutoff order preserves peer ties; the outer order stabilizes their presentation.
Success check
Both severity-five events and all five severity-four cutoff peers are returned.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| event_id | stream | severity |
|---|---|---|
| 101 | payments | 5 |
| 104 | orders | 5 |
| 103 | inventory | 4 |
| 106 | orders | 4 |
| 107 | payments | 4 |
| 108 | orders | 4 |
| 109 | inventory | 4 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
ORDER BY & Sorting
Practice deterministic SQL ordering with tie-breakers, custom priorities, NULL placement, expressions, joined data, aggregates, and portable top-N patterns.
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.