SQL 2005: Select top N, group by ID with joins
- by Suzy Fresh
I'm having real difficulty with a query involving 3 tables. I need to get the 3 newest users per department grouped by department names. The groups should be sorted by the users.dateadded so the department with the newest activity is first. The users can exist in multiple departments so Im using a lookup table that just contains the userID and deptID. My tables are as follows.
Department - depID|name
Users - userID|name|dateadded
DepUsers - depID|userID
The output I need would be
Receiving
John Doe - 4/23/2010
Bill Smith - 4/22/2010
Accounting
Steve Jones - 4/22/2010
John Doe - 4/21/2010
Auditing
Steve Jones - 4/21/2010
Bill Smith - 4/21/2010
