SQL Join to only the maximum row puzzle

Question

Given the following example data:

  Users
+--------------------------------------------------+
| ID | First Name | Last Name | Network Identifier |
+--------------------------------------------------+
| 1  | Billy      | O'Neal    | bro4               |
+----+------------+-----------+--------------------+
| 2  | John       | Skeet     | jsk1               |
+----+------------+-----------+--------------------+

 Hardware
+----+-------------------+---------------+
| ID | Hardware Name     | Serial Number |
+----+-------------------+---------------+
| 1  | Latitude E6500    | 5555555       |
+----+-------------------+---------------+
| 2  | Latitude E6200    | 2222222       |
+----+-------------------+---------------+

 HardwareAssignments
+---------+-------------+-------------+
| User ID | Hardware ID | Assigned On |
+---------+-------------+-------------+
| 1       | 1           | April 1     |
+---------+-------------+-------------+
| 1       | 2           | April 10    |
+---------+-------------+-------------+
| 2       | 2           | April 1     |
+---------+-------------+-------------+
| 2       | 1           | April 11    |
+---------+-------------+-------------+

I'd like to write a SQL query which would give the following result:

+--------------------+------------+-----------+----------------+---------------+-------------+
| Network Identifier | First Name | Last Name | Hardware Name  | Serial Number | Assigned On |
+--------------------+------------+-----------+----------------+---------------+-------------+
| bro4               | Billy      | O'Neal    | Latitude E6200 | 2222222       | April 10    |
+--------------------+------------+-----------+----------------+---------------+-------------+
| jsk1               | John       | Skeet     | Latitude E6500 | 5555555       | April 11    |
+--------------------+------------+-----------+----------------+---------------+-------------+

My trouble is that the maximum "Assigned On" date for each user needs to be selected for each individual user and used for the actual join ...

Is there a clever way accomplish this in SQL?

Answer 1

select * from Users as u
inner join HardwareAssignments as ha
    on u.id = ha.userid
inner join Hardware as h
    on uh.hardwareid = h.id
where 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.

Answer 2

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

Answer 3

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.