Tutorial :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  )  


Solution:1

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  


Solution:2

Simpler:

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


Solution: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


Solution:4

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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »