SQL 2005: Select Top N, Group By ID With Joins
I am having difficulty with a query that has 3 tables. I need to get 3 new users per department, grouped by department name. The groups must be sorted with users.dateadded, so there will be the department with the newest activity first. Users can exist in multiple departments, so I am using a lookup table that simply contains the user id and deptID. My tables are as follows.
Department - depID | name
Users - userID | name | dateadded
DepUsers - depID | userID
The output I need will be
Reception
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
a source to share
This should give you the 3 most recently added users for each department (using 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 understand exactly what you want the result to look like, but I think this result set gives you the opportunity to start where you are going.
a source to share
Try the following:
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
a source to share