SELECT ei.empname,ewh.deptid,ewh.fromdate,ewh.todate
FROM EmployeeInfo ei
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY fromdate DESC,todate DESC) AS Seq,*
            FROM EmployeeWorkHistory
           )ewh
ON ewh.empid = ei.Emp_id
AND ewh.Seq=2

quote:

---

Originally posted by visakh16

looks like this

SELECT ei.empname,ewh.deptid,ewh.fromdate,ewh.todate
FROM EmployeeInfo ei
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY fromdate DESC,todate DESC) AS Seq,*
            FROM EmployeeWorkHistory
           )ewh
ON ewh.empid = ei.Emp_id
AND ewh.Seq=2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---