SQL Server query. JOIN by latest date

I have 3 tables:

UnitInfo(UnitID, ...), 
UnitList(UnitID, ...) 
UnitMonitoring(RecordID,UnitID, EventDate, ...)

UnitList is a subset of UnitInfo (in terms of data and in terms of columns). UnitMonitoring receives records time to time pertaining to UnitList (for every UnitID in UnitMonitoring we will have many records) filling EventDate. (UnitInfo has extended info).

I can't figure how to build a query so that for every UnitID in UnitList I get开发者_如何学编程 UnitMonitoring record such that EventDate is the latest one.

So far I have

SELECT a.UnitID, a.Name, b.EventDate 
FROM UnitInfo a INNER JOIN UnitMonitoring b 
WHERE a.UnitID IN (SELECT UnitID FROM UnitList) 

which yields all records from UnitMonitoring

SELECT  ul.unitId, um.*
FROM    UnitList ul
OUTER APPLY
        (
        SELECT  TOP 1 *
        FROM    UnitMonitoring umi
        WHERE   umi.UnitID = ul.unitID
        ORDER BY
                EventDate DESC
        )

This will handle the duplicates correctly and will return all units (those with no records in UnitMonitoring will have NULL values in corresponding fields)

I chose to go with a Common Table Expression (CTE) to apply a ranking function (ROW_NUMBER):

;WITH NumberedMonitoring as (
     SELECT RecordID,UnitID, EventDate, ...
          ROW_NUMBER() over (PARTITION BY UnitID ORDER BY EventDate desc) rn
     FROM UnitMonitoring
)
SELECT * FROM
    UnitList ul
        inner join
    NumberedMonitoring nm
        on
            ul.UnitID = nm.UnitID and nm.rn = 1

But there are many different solutions (the above could also be done using a subselect).

Common Table Expressions (quoting from above link):

A common table expression (CTE) can be thought of as a temporary result set

That is, it lets you write a bit of the query first. In this case, I'm using it because I want to number the rows (using the ROW_NUMBER function). I'm telling it to restart the numbering for each UnitID (PARTITION BY UnitID), and within each unit ID, I want the rows numbered based on the EventDate descending (ORDER BY EventDate desc). This means that the row that receives row number 1 (within each UnitID partition) is the most recent row.

In the following select, I'm able to treat my CTE (NumberedMonitoring) as if it's any other table. So I'm just joining it to the UnitList table, and ensuring as part of the join conditions that I'm only selecting row number 1 (rn = 1)

Try:

Select M.* 
From UnitList L 
  Join UnitMonitoring M
    On M.UnitId = L.UnitId
Where M.EventDate = 
   (Select Max(EventDate) From UnitMonitoring 
    Where UnitId = M.UnitId)

If There are multiple records with the same UnitId and EventDate, then you can still use this technique, but you need to filter on a unique field, say the PK field in UnitMonitoring in this case is named PkId.

Select M.* 
From UnitList L 
  Join UnitMonitoring M
    On M.UnitId = L.UnitId
Where M.PkId = 
   (Select Max(PkId) From UnitMonitoring iM
    Where UnitId = M.UnitId
        And EventDate = 
             (Select Max(EventDate) From UnitMonitoring 
              Where UnitId = M.UnitId))

SELECT a.UnitID, a.Name, MAX(b.EventDate) 
FROM UnitInfo a 
INNER JOIN UnitMonitoring b 
WHERE a.UnitID IN (SELECT UnitID FROM UnitList)
GROUP BY a.UnitID, a.Name