Nice example!

Alternativly you could use a cursor to make it more readable and improve performance over a nested query. That said i dont like cursors and do prefer common table expressions!

SET ROWCOUNT 0

go

IF OBJECT_ID('tempdb..#ProductVersion') IS NULL

BEGIN

CREATE TABLE #ProductVersion

(

ProductID int NOT NULL,

Version int NOT NULL,

MinorVersion int NOT NULL,

ReleaseVersion int NOT NULL,

StandardCost numeric(30, 4) NOT NULL

)

END

DECLARE @ProductId INT

DECLARE A CURSOR FOR

SELECTProductId

FROMProductVersion

GROUP BY ProductId

ORDER BY ProductId

OPEN A

FETCH NEXT FROM A

INTO @ProductId

WHILE@@FETCH_STATUS = 0

BEGIN

INSERT INTO #ProductVersion

SELECTTOP 1 ProductId,

Version,

MinorVersion,

ReleaseVersion,

StandardCost

FROMProductVersion

WHEREProductId = @ProductId

ORDER BY ProductId ASC,

Version DESC,

MinorVersion DESC,

ReleaseVersion DESC

FETCH NEXT FROM A

INTO @ProductId

END

CLOSE A

DEALLOCATE A

SELECT * FROM #ProductVersion

DROP TABLE #ProductVersion