SQL Select Best practice

Question

The following works, I'm just wondering if this is the correct approach to finding the latest value for each audit field.

USE tempdb
CREATE Table Tbl(
TblID Int,
AuditFieldID Int,
AuditValue Int,
AuditDate Date
)
GO
INSERT INTO Tbl(TblID,AuditFieldID,AuditValue,AuditDate) VALUES(1,10,101,'1/1/2001')
INSERT INTO Tbl(TblID,AuditFieldID,AuditValue,AuditDate) VALUES(2,10,102,'1/1/2002')
INSERT INTO Tbl(TblID,AuditFieldID,AuditValue,AuditDate) VALUES(3,20,201,'1/1/2001')
INSERT INTO Tbl(TblID,AuditFieldID,AuditValue,AuditDate) VALUES(4,20,202,'1/1/2009')


SELECT AuditFieldID,AuditValue,AuditDate
FROM Tbl A
WHERE TblID=
(SELECT TOP 1 TblID
FROM Tbl
WHERE AuditFieldID=A.AuditFieldID
ORDER BY AuditDate DESC
)

Answer 1

Simpler:

SELECT top 1 AuditFieldID,AuditValue,AuditDate FROM Tbl order by AuditDate DES

Answer 2

you don't need the where statement as you are already selecting from tbl A AND selecting on the same field.

Answer 3

There are various methods for doing this. Different methods perform differently. I encourage you to look at this blog which explains the various methods.

Including an Aggregated Column's Related Values

Answer 4

Aggregate/ranking to get key and latest date, join back to get value.

This assumes SQL Server 2005+

DECLARE @tbl Table (
TblID Int,
AuditFieldID Int,
AuditValue Int,
AuditDate Date
)

INSERT INTO @tbl(TblID,AuditFieldID,AuditValue,AuditDate) VALUES(1,10,101,'1/1/2001')
INSERT INTO @tbl(TblID,AuditFieldID,AuditValue,AuditDate) VALUES(2,10,102,'1/1/2002')
INSERT INTO @tbl(TblID,AuditFieldID,AuditValue,AuditDate) VALUES(3,20,201,'1/1/2001')
INSERT INTO @tbl(TblID,AuditFieldID,AuditValue,AuditDate) VALUES(4,20,202,'1/1/2009')


;WITH cLatest AS
(
    SELECT
        ROW_NUMBER() OVER (PARTITION BY AuditFieldID ORDER BY AuditDate DESC) AS Ranking,
        AuditFieldID, AuditDate
    FROM
        @tbl
)
SELECT
    A.AuditFieldID, A.AuditValue, A.AuditDate
FROM
    @tbl A
    JOIN
    cLatest C ON A.AuditFieldID = C.AuditFieldID AND A.AuditDate = C.AuditDate
WHERE
    C.Ranking = 1