Comparison between "the most recent record" approaches

select_latest_version.sql

SET NOCOUNT ON
SET STATISTICS IO ON

DECLARE @t TABLE (
	Id INT IDENTITY(1,1)
	, MyValue INT
	, MyDate DATE
)

INSERT INTO @t VALUES(1,'2015-01-01')
INSERT INTO @t VALUES(1,'2010-01-01')
INSERT INTO @t VALUES(2,'2010-01-01')
INSERT INTO @t VALUES(2,'2012-01-01')
INSERT INTO @t VALUES(2,'2013-01-01')

SELECT * FROM @t

-- v1: MAX (CONS: can't use Id VS outer tables)
PRINT 'V1'
;WITH V1 AS (
	SELECT	
		MyValue
		, MAX(MyDate)		AS MyDate
	FROM @t
	GROUP BY MyValue
)
SELECT 'V1',* FROM V1

-- v2: PARTITION BY (CONS: can't use Id VS outer tables)
PRINT 'V2'
;WITH V2 AS (
	SELECT	
		ROW_NUMBER() OVER (PARTITION BY MyValue ORDER BY MyDate DESC) AS Nbr
		, MyValue
		, MyDate
	FROM @t
)
SELECT 'V2',* FROM V2 WHERE Nbr = 1 -- cant use Id

-- v3: inner select (lot of logical reads, one per TABLE row. Smaller worktable. CAN use Id. Good for small datasets)
PRINT 'V3'
; WITH V3 AS (
	SELECT	
		Id
		, MyValue
		, MyDate
	FROM @t TMP
	WHERE Id = (
		SELECT TOP 1 Id
		FROM @t _TMP
		WHERE _TMP.MyValue = TMP.MyValue
		ORDER BY MyDate DESC
	)
)
SELECT 'V3',* FROM V3

-- v4: cross apply (lot of logical reads, one per MAX row. Smaller worktable. CAN use Id. Good for small datasets)
PRINT 'V4'
; WITH V4 AS (
	SELECT	
		TMP.Id
		, MyValue
		, MyDate
	FROM @t TMP
	CROSS APPLY (
		SELECT TOP 1 Id
		FROM @t _TMP
		WHERE _TMP.MyValue = TMP.MyValue
		ORDER BY MyDate DESC
	) CA
	WHERE CA.Id = TMP.Id
)
SELECT 'V4',* FROM V4