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.