SQL Server A simple SQL Select Query (Employee & EmployeeHistory) [duplicate]

kb5ga3dv  于 2023-03-28  发布在  其他
关注(0)|答案(1)|浏览(101)

This question already has answers here:

Get top 1 row of each group (19 answers)
Closed 3 days ago.

I have two DB tables Employee and EmployeeHistory (Sample below).

Id  Name

1   John

2   Robert

3   Allan

Id  EmployeeId  Status

1   1           Active

2   1           Blocked

3   1           Active

4   2           Active

5   2           Blocked

6   3           Active

7   3           Blocked

8   3           Active

I need to select only those employees (with EmployeeHistory record) whose last (Latest) status is Active in EmployeeHistory table (John and Allan in this case). Records in both tables are sequential (i.e. from small to large) by Id. I have written this query but this is not working as per expectations.

SELECT E.Id, E.Name, EH.Status FROM
Employee E INNER JOIN EmployeeHistory EH ON EH.EmployeeId = E.Id
WHERE 
(SELECT TOP 1 Status FROM EmployeeHistory WHERE EmployeeId = E.Id 
ORDER BY Id DESC) = 'Active'

Please if someone can correct the query? I am using SQL Server .

Thanks!

kyvafyod

kyvafyod1#

SELECT e.*
FROM (
    SELECT EmployeeID, Status
       , row_number() over (PARTITION BY EmployeeID ORDER BY ID DESC) rn
    FROM EmployeeHistory
) eh
INNER JOIN Employee e on e.Id = eh.EmployeeID
WHERE eh.rn = 1 AND eh.Status = 'Active'

相关问题