Retrieve Last Record for each Group in SQL Server

In this article, we will show you how to Retrieve the Last Record for each Group in SQL Server with an 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 record from each SQL Server group.

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

Retrieve Last Record in SQL Server Example 2

In this example, we show you how to retrieve the last row in each Group using a subquery.

-- Select First Row in each group

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