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

In this example, we used SQL CTE and the SQL ROW_NUMBER () function to rank each record present in a partition.

You can also use the remaining Ranking functions from the SQL Server tutorial page, 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. Refer to the SQL GROUP BY and SQL subquery articles.

-- 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