Sql query for many to one
I have a table with 3 columns
table: StaffDepartmentAssgnment
StaffId DepartmentId AssignedFromDate
S1 Dept1 2013-02-08
S2 Dept1 2013-02-08
S3 Dept2 2013-02-01
S1 Dept2 2013-02-01
I want to know all StaffIds that are currently in Dept2. How can I write a request?
+3
Prachi pant
source
to share
2 answers
This is a database independent solution
select * from
(
select StaffId, max(AssignedFromDate) as adate
from StaffDepartmentAssgnment
group by staffid
) x
inner join StaffDepartmentAssgnment y
on y.staffid = x.staffid and adate = y.AssignedFromDate
where DepartmentId = 'dept2'
demo SQLFiddle
+3
juergen d
source
to share
May be:
SELECT DISTINCT sd.StaffId
FROM StaffDepartmentAssgnment sd
WHERE sd.DepartmentId = 'Dept2'
Update
S1 is not in Dept2 now, it was moved to Dept1 ... I need to pick up the top AssignedFromDate
Since you are using SQL-Server, you can use CTE
with the function ROW_NUMBER
:
WITH CTE AS (
SELECT sd.staffid, sd.DepartmentId,
rn = Row_number() OVER (
Partition BY sd.staffid
ORDER BY assignedfromdate DESC)
FROM staffdepartmentassgnment sd)
SELECT staffid
FROM cte
WHERE rn = 1
AND DepartmentId = 'Dept2'
Demo
+2
Tim Schmelter
source
to share