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:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: https://rebrand.ly/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): https://rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): https://rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): https://rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): https://rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): https://rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
----
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:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: http://idodata.com/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): http://idodata.com/database-fundament...
SQL Server Essential in an Hour: http://idodata.com/sql-server-essenti...
70-462 SQL Server Database Administration (DBA): http://idodata.com/sql-server-databas...
DP-300: Administering Relational Databases: http://idodata.com/dp-300-administeri...
Microsoft SQL Server Reporting Services (SSRS): http://idodata.com/microsoft-sql-serv...
SQL Server Integration Services (SSIS): http://idodata.com/sql-server-integra...
SQL Server Analysis Services (SSAS): http://idodata.com/sql-server-ssas-mu...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
1Z0-071 Oracle SQL Developer – certified associate: http://idodata.com/iz0-071-oracle-sql...
SQL for Microsoft Access: http://idodata.com/sql-for-microsoft-...
DP-900: Microsoft Azure Data Fundamentals: http://idodata.com/dp-900-microsoft-a...
---
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.
