SQL Join to only the maximum row puzzle SQL Join to only the maximum row puzzle sql sql

SQL Join to only the maximum row puzzle

SELECT U.NetworkIdentifier, U.FirstName, U.LastName,       H.HardwareName, H.SerialNumber  FROM (SELECT UserID, MAX(AssignedOn) LastAssignment          FROM HardwareAssignments         GROUP BY UserID) AS T  JOIN HardwareAssignments AS HA       ON HA.UserId = T.UserID AND HA.AssignedOn = T.LastAssignment  JOIN Users AS U ON U.ID = HA.UserID  JOIN Hardware AS H ON H.ID = HA.HardwareID ORDER BY U.NetworkIdentifier;

The difference between this and Justin Niessner's answer is where the sub-query appears; here, I've created it in the FROM clause. This pretty much guarantees that it is executed once. When there's a correlated sub-query in the WHERE clause as in Justin's answer, it is possible that the optimizer will execute the sub-query once for each row - which is more expensive when the tables are big. A really good optimizer might flatten things so that the two are equivalent.

select * from Users as uinner join HardwareAssignments as ha    on u.id = ha.useridinner join Hardware as h    on uh.hardwareid = h.idwhere ha.AssignedOn = (select max(assignedon)                       from HardwareAssignments as ha2                       where ha.userid = ha2.userid)

That could get you close. Not sure if it's exact.

Use group by and max to filter the results of the join.