Update and Group by

Last Post 13 Jan 2009 09:15 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
rchua
New Member
New Member

--
13 Jan 2009 06:58 AM
Employee ----------- Dept ----------- counter
emp1 ---------------- 1 ---------------- 1
emp2 ---------------- 1 ---------------- 2
emp3 --------------- 1 ---------------- 3
emp4 --------------- 2 ---------------- 1
emp5 --------------- 2 ---------------- 2
emp6 --------------- 3 ---------------- 1
emp7 --------------- 3 ---------------- 2
emp8 -------------- 3 ---------------- 3

I need help writing and SQL update query. The data consists on employee and Dept. I need an update statement that will update the counter column GROUP by dept. (The output should look like the one on top)
SQLUSA
New Member
New Member

--
13 Jan 2009 09:15 AM
You can do an UPDATE GROUP BY based on the following query. In fact it uses PARTITION BY instead of GROUP BY:

SELECT D.DepartmentID, E.EmployeeID,
SeqNo = row_number() over (partition by d.DepartmentID order by E.EmployeeID)
FROM HumanResources.EmployeeDepartmentHistory EDH
JOIN HumanResources.Department D
ON d.DepartmentID = EDH.DepartmentID
and EDH.EndDate is null
JOIN HumanResources.Employee E
ON EDH.EmployeeID = E.EmployeeID

/* Partial results

DepartmentID EmployeeID SeqNo
1 3 1
1 9 2
1 11 3
1 12 4
1 267 5
1 270 6
2 4 1
*/

Kalman Toth, SQL Server 2008 Training
http://www.sqlusa.com/order2008


Acceptable Use Policy
---