Practice Activity: How to retrieve the second row from a dataset in Microsoft SQL Server

How can you retrieve not the highest value, but the second highest value?
My SQL Server Udemy courses are:
----
In this practice activity, you'll learn how to retrieve the second row from a dataset in Microsoft SQL Server. This is an important skill for data analysts and programmers, and we'll take you through the steps step-by-step.

If you're new to Microsoft SQL Server or need to learn how to retrieve data from a dataset, then this practice activity is for you! I'll teach you the basics of retrieving data from a dataset in Microsoft SQL Server, and by the end you'll be ready to go out and use this skill in your work!

It is easy to retrieve the highest value - just use MAX. But how can you get the second highest value (or the Nth value)?
If you want to do this as a Practice Activity, then the starting code is as follows:
DROP TABLE IF EXISTS tblHouseprices
GO

CREATE TABLE tblHousePrices(
[PriceDate] [date] NOT NULL,
[Region] [varchar](20) NOT NULL,
[Price] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tblHousePrices VALUES
('2024-06-01', 'Greater Manchester', 346251),
('2024-07-01', 'East Midlands', 312289),
('2024-07-01', 'West Midlands', 365274),
('2024-08-01', 'East Midlands', 328072),
('2024-08-01', 'Greater Manchester', 353617),
('2024-09-01', 'East Midlands', 339697),
('2024-09-01', 'West Midlands', 370206),
('2024-09-01', 'Greater Manchester', 358902),
('2024-10-01', 'West Midlands', 376596),
('2024-10-01', 'Greater Manchester', 357744),
('2024-11-01', 'West Midlands', 371699);
----
Links to my website are:
---
You can use either RANK or ROW_NUMBER to number each of the rows, and then use a WITH to temporarily save it, and then use a WHERE on the saved data.

[RockRockAndDogs]

Your voice sounds so familiar! I feel I have heard it in some other YouTube channel.

[rockymarquiss]

You aren't clear on your wording. You refer to second highest for the month but you use the date. To be consistent with second highest for the month:

with cte_second as (
select *, rank() OVER (partition by year(pricedate), month(pricedate) order by price desc) ranking from tblHouseprices
)
select * from cte_second where ranking = 2;

Using your verbal description of by months and if the third record was '2024-07-02', your code breaks as you wouldn't have anything in July either - but with the above code you'll get the results as you say you're after - second highest per month.