SQL Second Highest Value
A classic — and there are three completely different ways to get it right.
What & Why
The Second Highest Value pattern is used to rank distinct values and select the second-highest position. This keeps the SQL aligned with one concrete business question and makes the result grain explicit before the query is reused.
See How It Works
BUSINESS QUESTION
Marketing wants every campaign tied for the second-highest spend.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
EXAMPLE QUERY
WITH ranked AS (
SELECT
id AS campaign_id,
name,
spend,
DENSE_RANK() OVER (ORDER BY spend DESC) AS spend_rank
FROM marketing.campaigns
)
SELECT campaign_id, name, spend
FROM ranked
WHERE spend_rank = 2
ORDER BY campaign_id;RESULT — second-highest campaign spend
| campaign_id | name | spend |
|---|---|---|
| 1 | Spring Launch | 55000.00 |
Spring Launch has the second distinct spend after Enterprise Search.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario