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: rebrand.ly/que...
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/dat...
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql...
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql...
SQL Server Integration Services (SSIS): rebrand.ly/sql...
SQL Server Analysis Services (SSAS): rebrand.ly/sql...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/mic...
----
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);
Негізгі бет Practice Activity - Retrieving the last row for each group in a table in SQL Server
Пікірлер: 16