**Table Employee** Id Name 1 EmpName1 2 EmpName2 3 EmpName3 **Table EmpDeptHistory** Id EmpId Dept Date 1 1 Housing 2015-03-02 2 2 Finance 2015-01-03 3 1 WareHouse 2015-05-02 4 2 Housing 2015-02-06 5 3 WareHouse 2015-02-02 6 1 Housing 2015-05-01 7 2 Finance 2015-01-02 8 2 Housing 2015-05-04 9 2 Finance 2015-05-02 10 1 WareHouse 2015-03-08 11 1 Housing 2015-02-20
I need find the recent dept with which every employee worked. Also I need to find for individual employee by passing EmpId
The following query returns only one employee and not all :(
SELECT e.id, edh.dept,edh.date FROM Employee e inner join (select top 1 eh.empid, eh.dept, eh.date from EmpDeptHistory eh order by eh.date desc) as edh on e.id=edh.empid
yes, I understand the top 1 will give the emp id based on date, hence only one employee details is show. I am not sure how to get all the employee recent department.
select e.id,edh.dept,edh.date from employee e inner join EmpDeptHistory edh on e.id = (Select eh.empid, eh.dept, eh.date from EmpDeptHistory eh where e.id=eh.empid order by eh.date desc)
The above throws
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.