Clever Geek Handbook

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


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


source


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


source






More articles:


All Articles