SQL 2005: Select top N, group by ID with joins

Question

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

Answer 1

This should give you the 3 last added users for each department (making use of the new ROW_NUMBER function in SQL 2005):

select * from (select  D.name, U.name, U.dateadded, ROW_NUMBER() over (PARTITION BY D.depID ORDER BY U.dateadded DESC) as ROWID from Department as D
join DepUsers as DU on DU.depID = D.depID
join Users as U on U.userID = DU.userID) as T
where T.ROWID <= 3

I didn't exactly understood how you wanted the output to look, but I guess this result set gives you a kick start to get where you're headed.

Answer 2

Try this:

WITH CTE AS
(
SELECT 
  ROW_NUMBER() OVER (PARTITION BY DepartmentName ORDER BY DateAdded DESC) AS RowNumber
  ,D.name AS DepartmentName
  ,U.name AS UserName
  ,U.dateadded AS DateAdded
FROM 
  DepUsers DU
INNER JOIN Users U
  ON DU.userID = U.userID
INNER JOIN Department D
  ON DU.depID = D.depID
)
SELECT
  DepartmentName
  ,UserName
  ,DateAdded
FROM CTE
WHERE RowNumber <= 3
ORDER BY DepartmentName, DateAdded DESC