0

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!

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Gulfam
  • 558
  • 6
  • 27
  • What DBMS are you using? Sql server, MySql, etc. Please update your post's tags. – Ryan Wilson Mar 24 '23 at 13:21
  • As this is not a code writing service, please update your question (using editable text, not images) with the SQL you’ve managed to write on your own and then ask a question about a specific issue you are facing – NickW Mar 24 '23 at 13:21
  • @RyanWilson I have updated the question. I am using SQL Server – Gulfam Mar 24 '23 at 14:09

1 Answers1

1
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'
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794