Retrieve Last Record for each Group in SQL Server

In this article, we will show you, How to Retrieve Last Record for each Group in SQL Server with example. For this, We are going to use the below shown data

Retrieve Last Record for each Group in SQL Server Example 1

In this example, we used CTE, and ROW_NUMBER Function to rank each record present in a partition. You can also use remaining Ranking functions, as per your requirements.

• First, partition the data by Occupation and assign the rank number using the yearly income.
• Next, it is going to select the last row from each group.

T-SQL CODE

USE [SQL Tutorial]
GO
WITH TopRows AS (
	SELECT [FirstName]
		  ,[LastName]
		  ,[Education]
		  ,[Occupation]
		  ,[YearlyIncome]
		  ,[Sales]
		  ,[HireDate]
		  ,ROW_NUMBER() OVER (
						 PARTITION BY [Occupation] 
						 ORDER BY [YearlyIncome] DESC
         		   ) AS [ROW NUMBER]
	  FROM [Employee]
)
SELECT * FROM TopRows
WHERE TopRows.[ROW NUMBER] <= 2

OUTPUT

Retrieve Last Record for each Group in SQL Server Example 2

In this example we will show you, How to retrieve last row in each Group using sub query.

T-SQL CODE

-- Select First Row in each SQL Group By group
USE [SQL Tutorial]
GO
SELECT * FROM (
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ROW_NUMBER() OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC
         	   ) AS [ROW NUMBER]
  FROM [Customers]
  ) groups
WHERE groups.[ROW NUMBER] = 1
ORDER BY groups.YearlyIncome DESC

OUTPUT

Thank You for Visiting Our Blog