Selecting records by group each having the max date respectively

Question

I have three tables of concern and a query which generates the following output from them, where each field is prepended by a letter representing the table of origin for demonstration purposes. What I would like instead, is a report with only the most recent data, i.e. I want only the row returned with the MAX(EntryDate), but I can't figure out how to accomplish this. I've tried, HAVING, aggregates, etc, but with no success. Either my approach is wrong, or I'm missing something syntactically.

s.ID    r.FirstID    m.ID   m.PartNum     m.Revision     r.EntryDate
26      42           13     NULL          A              2012-03-25 15:32:00
26      42           17     820044444     B              2012-03-27 09:48:00
26      42           14     820056789     C              2012-03-28 12:19:00

Desired Result:

s.ID    r.FirstID    m.ID   m.PartNum     m.Revision     r.EntryDate
26      42           14     820056789     C              2012-03-28 12:19:00

For a little additional background, the r.FirstID represents a grouping of records all related to each other, so for each grouping I wish to return only the record with the most recent data.

Here is my query that I have so far:

select s.ID,r.FirstID,m.ID,m.PartNum,m.Revision,r.EntryDate from StatusLog s
    left join (select FirstID,PartInfoID from Request where PartInfoID is not null group by FirstID,PartInfoID) r on s.FirstID= r.FirstID
    --required filtering here? Or move this extra join to the inner query?
    left join PartInfo m on r.PartInfoID = m.ID

I'm using SQL Server for my DBMS.

Answer 1

The easiest way will be to use ROW_NUMBER() (and I am using a CTE for simplicity). Let me know if you need me to explain what is happening here :)

WITH myCTE
AS
(
    SELECT s.ID AS sID, r.FirstID, m.ID AS mID, m.PartNum, m.Revision, 
        r.EntryDate, 
        ROW_NUMBER() OVER 
            (PARTITION BY r.FirstID ORDER BY r.EntryDate DESC) AS RowNumber
    FROM StatusLog s
        LEFT JOIN 
        (
            SELECT FirstID, PartInfoID 
            FROM Request 
            WHERE PartInfoID IS NOT NULL 
            GROUP BY FirstID,PartInfoID
        ) AS r 
            ON s.FirstID= r.FirstID
        LEFT JOIN PartInfo m 
            ON r.PartInfoID = m.ID
)
SELECT sID,FirstID,mID,PartNum,Revision,EntryDate
FROM myCTE 
WHERE RowNumber = 1;