SQLServerPedia

Sounds simple, right?  Just grab the max.  But what if you want to use a single T-SQL operation to fetch the most recent record and some of its attributes?

StorminSpank asked:

@BrentO SQL Question. 4 columns, ID, Date, Info1, Info2. ID has multiple entries. Want Latest Date for Distinct ID, but also info1 and 2.

Let’s say our table schema is:

CREATE TABLE [dbo].[TestTable](
	[id] [INT] NOT NULL,
	[create_date] [DATE] NOT NULL,
	[info1] [VARCHAR](50) NOT NULL,
	[info2] [VARCHAR](50) NOT NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
	[id] ASC, [create_date] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

And let’s populate it with some data:

INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (1, '1/1/09', 'Blue', 'Green')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (1, '1/2/09', 'Red', 'Yellow')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (1, '1/3/09', 'Orange', 'Purple')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (2, '1/1/09', 'Yellow', 'Blue')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (2, '1/5/09', 'Blue', 'Orange')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (3, '1/2/09', 'Green', 'Purple')
INSERT INTO dbo.TestTable (id, create_date, info1, info2)
VALUES (3, '1/8/09', 'Red', 'Blue')

Now let’s get out the most recent record for each ID:

SELECT tt.*
  FROM dbo.TestTable tt
    LEFT OUTER JOIN dbo.TestTable ttNewer 
    ON tt.id = ttNewer.id AND tt.create_date < ttNewer.create_date
  WHERE ttNewer.id IS NULL

That left outer join is looking for any newer TestTable records, but the where clause makes sure there aren’t any. If you switch it around to “IS NOT NULL”, you’d get the exact opposite - you’d get all of the older records.

And remember, ladies and gentlemen, do as I say and not as I do - never SELECT *.