TSQL Search for the Last Record in a Group

In this article I’ll show how to get the most recent record for a group of records in a SQL Server table. There are a number of ways to do this including CTEs and nested subqueries. Since nested subqueries are the most widely supported across the different versions of SQL Server, I consider them the most robust/straightforward way to get the job done, so in this article I will show you how to solve this problem using a nested subquery in either your WHERE or your FROM clause.

At the end of this article I’ll give the full working example Pet shop table and query that you can copy and paste into a SQL Server Management Studio (SSMS) session to try out the code for yourself.

If you find this article useful, be sure to check out my other SQL Server related articles for more tips.

Overview

To illustrate the example, let’s take the case of an imaginary pet shop in the 1990s. They have just put into place a SQL Server database to maintain the status of each of the animals that they get, vaccinate, and then sell. So far they have records for three animals: a cat named Mittens, a dog named Spot, and a rabbit named Hoppy Bunny.

The owner of the pet shop has been diligently entering the status of each pet into his new database and now wants a report about the status of each pet. He got Mittens the cat just last month and sold her recently, but what about Spot and Hoppy Bunny?

Here is what the data in the pet shop owner’s database table looks like, and below is what he wants to see as a result… he doesn’t care about the history of each pet, he just wants to see the last status for each:

Solution using a Nested Subquery in the WHERE clause

As I mentioned before, the nested subquery in the WHERE clause is the most straightforward in this case. The Pet shop owner can write his search as follows:

SELECT [PetName],[Vaccinated],[StatusInStore],[EntryDate]
FROM #myTest WHERE Id IN (
SELECT MAX(Id) FROM #myTest Group By PetName
)

From this query you can see that the key ingredient is to select the MAX of each primary key Id to get the latest status of each pet. The GROUP BY part of the query allows us to uniquely identify each pet to include in the search results.

But what if there is no AutoIncrementing Primary Key?

It can be the case that either your table was not designed with an auto-incrementing primary key, or the primary key can’t be relied on to order results chronologically. In this case the update date is  a better key to drive your query from.

There are two simple ways to get your Pet status results if there is no primary key in your table.

1. The first, way is to tweak the query I showed above to create your own primary key using a concatenation of the Entry Date and the Pet name. Here’s an example of that would look like:

   SELECT [PetName],[Vaccinated],[StatusInStore],[EntryDate]
   FROM #myTest WHERE 
   Convert(VarChar(10),CAST([EntryDate] AS DATETIME),126)+[PetName]
    IN (
   SELECT MAX(Convert(VarChar(10),CAST([EntryDate] AS DATETIME),126)+[PetName]) FROM #myTest Group By [PetName]
   )

2. The second way is a bit cleaner in that you do not need to create your own new key by concatenating two columns. Instead you can move your nested subquery to the FROM clause and filter the search results as follows:

   SELECT z.[PetName],z.[Vaccinated],z.[StatusInStore],z.[EntryDate]
   FROM #myTest z 
   INNER JOIN (
   	SELECT [PetName],Max([EntryDate]) [maxdt] FROM #myTest GROUP BY [PetName]) AS [tmptbl]
   	ON z.[PetName] = [tmptbl].[PetName]
   WHERE z.[PetName] = [tmptbl].[PetName] and z.[EntryDate] =  [tmptbl].[maxdt]

The Fully Working Example Code

Here is the example code for the Pet shop I’ve been describing above. You can copy and paste this into an SSMS session to play around with the code. Notice that it includes all three example queries.

-- Create the temporary table
CREATE TABLE #myTest(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PetName] [varchar](50) NOT NULL,
	[Vaccinated] [varchar](3) NOT NULL,	
	[StatusInStore] [varchar](50) NOT NULL,	
	[EntryDate] DateTime NOT NULL
)
GO

-- Populate the table. Insert three duplicates of 111111, two of 222222, and four of 4444
INSERT INTO #myTest ([PetName],[Vaccinated],[StatusInStore],[EntryDate]) VALUES ('Mittens','No','New','1/1/1990')
INSERT INTO #myTest ([PetName],[Vaccinated],[StatusInStore],[EntryDate]) VALUES ('Mittens','Yes','For Sale','1/5/1990')
INSERT INTO #myTest ([PetName],[Vaccinated],[StatusInStore],[EntryDate]) VALUES ('Spot','No','New','1/12/1990')
INSERT INTO #myTest ([PetName],[Vaccinated],[StatusInStore],[EntryDate]) VALUES ('Spot','Yes','For Sale','1/14/1990')
INSERT INTO #myTest ([PetName],[Vaccinated],[StatusInStore],[EntryDate]) VALUES ('Mittens','Yes','Sold','1/15/1990')
INSERT INTO #myTest ([PetName],[Vaccinated],[StatusInStore],[EntryDate]) VALUES ('Hoppy Bunny','No','New','2/1/1990')
GO

-- Select the list of pets showing their latest status in the pet store
SELECT * FROM #myTest
GO
SELECT [PetName],[Vaccinated],[StatusInStore],[EntryDate]
FROM #myTest WHERE Id IN (
SELECT MAX(Id) FROM #myTest Group By PetName
)
-- The query assuming no primary key Id, and filtered using a nested subquery in the WHERE clause and a constructed primary key
SELECT [PetName],[Vaccinated],[StatusInStore],[EntryDate]
FROM #myTest WHERE 
Convert(VarChar(10),CAST([EntryDate] AS DATETIME),126)+[PetName]
 IN (
SELECT MAX(Convert(VarChar(10),CAST([EntryDate] AS DATETIME),126)+[PetName]) FROM #myTest Group By [PetName]
)
-- The query assuming no primary key Id, and using a nested subquery in the FROM clause without needing to construct a key
SELECT z.[PetName],z.[Vaccinated],z.[StatusInStore],z.[EntryDate]
FROM #myTest z 
INNER JOIN (
	SELECT [PetName],Max([EntryDate]) [maxdt] FROM #myTest GROUP BY [PetName]) AS [tmptbl]
	ON z.[PetName] = [tmptbl].[PetName]
WHERE z.[PetName] = [tmptbl].[PetName] and z.[EntryDate] =  [tmptbl].[maxdt]
 
-- Drop the temporary table
DROP TABLE #myTest
GO