Practice Activity - Retrieving the last row for each group in a table in SQL Server
11.2 هزار بار بازدید -
3 سال پیش
-
You may have a table
You may have a table with various products or areas. How can you extract the latest data for each group?
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 video, we will be looking at fictious house prices per region. However, the data is not for the same dates in each region.
How can you get the latest row for each group? So you need the latest row for East Midlands, the latest row for West Midlands etc.
There are at least three different ways for doing this:
1. In the WHERE clause, using a correlated query,
2. Using a JOIN, and
3. Using the ROW_NUMBER function.
Here is the code to create and populate the table. If you want to try it yourself, why not check your solutions with mine?
----
CREATE TABLE tblHouseprices (
PriceDate date NOT NULL,
Region varchar(20) NOT NULL,
price int NOT NULL);
GO
INSERT INTO tblHouseprices
VALUES
('2022-06-01', 'Greater Manchester', 346251),
('2022-07-01', 'East Midlands', 312289),
('2022-07-01', 'West Midlands', 365274),
('2022-08-01', 'East Midlands', 328072),
('2022-08-01', 'Greater Manchester', 353617),
('2022-09-01', 'East Midlands', 339697),
('2022-09-01', 'West Midlands', 370206),
('2022-09-01', 'Greater Manchester', 358902),
('2022-10-01', 'West Midlands', 376596),
('2022-10-01', 'Greater Manchester', 357744),
('2022-11-01', 'West Midlands', 371699);
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 video, we will be looking at fictious house prices per region. However, the data is not for the same dates in each region.
How can you get the latest row for each group? So you need the latest row for East Midlands, the latest row for West Midlands etc.
There are at least three different ways for doing this:
1. In the WHERE clause, using a correlated query,
2. Using a JOIN, and
3. Using the ROW_NUMBER function.
Here is the code to create and populate the table. If you want to try it yourself, why not check your solutions with mine?
----
CREATE TABLE tblHouseprices (
PriceDate date NOT NULL,
Region varchar(20) NOT NULL,
price int NOT NULL);
GO
INSERT INTO tblHouseprices
VALUES
('2022-06-01', 'Greater Manchester', 346251),
('2022-07-01', 'East Midlands', 312289),
('2022-07-01', 'West Midlands', 365274),
('2022-08-01', 'East Midlands', 328072),
('2022-08-01', 'Greater Manchester', 353617),
('2022-09-01', 'East Midlands', 339697),
('2022-09-01', 'West Midlands', 370206),
('2022-09-01', 'Greater Manchester', 358902),
('2022-10-01', 'West Midlands', 376596),
('2022-10-01', 'Greater Manchester', 357744),
('2022-11-01', 'West Midlands', 371699);
3 سال پیش
در تاریخ 1400/11/14 منتشر شده
است.
11,286
بـار بازدید شده