SQL Join to only the maximum row puzzle

Posted by Billy ONeal on Stack Overflow See other posts from Stack Overflow or by Billy ONeal
Published on 2010-04-08T16:25:15Z Indexed on 2010/04/08 16:33 UTC
Read the original article Hit count: 480

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql-query