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

kb5ga3dv 于 2023-03-28 发布在其他

关注(0)|答案(1)|浏览(104)

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!

sql-server

来源：https://stackoverflow.com/questions/75834126/a-simple-sql-select-query-employee-employeehistory

1条答案

按热度按时间

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'

赞(0）回复(0）举报 2023-03-28

我来回答

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

1条答案

相关问题

热门标签

最新问答