Reputation: 113
I need help on a very basic SQL join concept, that I just can't seem to get right.
I have an employee table and a position table. The employee table is like so:
EmpID Name
1 Jane Jones
2 Bob Smith
3 Jim Adams
The position table is like this:
PosID EmpID Position DateFilled
1 1 Sales 1/2/2012
2 2 HR 4/5/2013
3 2 Mgmnt 6/1/2014
4 2 Sr. Mgmnt 7/5/2015
5 3 IT Support 4/6/2014
6 3 IT Devel. 5/11/2015
How can I get the following output:
EmpID Name Position DateFilled
1 Jane Jones Sales 1/2/2012
2 Bob Smith Sr. Mgmnt 7/5/2015
3 Jim Adams IT Devel. 5/11/2015
So, in other words, how do I join to get only the record with the max DateFilled column from the position table to join with the corresponding record in the employee table? Any assistance would be greatly appreciated.
Upvotes: 0
Views: 1152
Reputation: 8188
try this
select temp.EmpID,(select position from Position where PosID =temp.PosID) position,DateFilled,Name from
(select EmpID,max(PosID) PosID,max(DateFilled) DateFilled
from position group by EmpID ) temp
inner join employee emp on emp.EmpID =temp.EmpID
Upvotes: 0
Reputation: 168806
You can do it using MAX() KEEP ( DENSE_RANK [FIRST|LAST] ... )
like this:
SELECT e.EmpId,
e.Name,
p.position,
p.datefilled
FROM employee e
INNER JOIN (
SELECT EmpID,
MAX( Position ) KEEP ( DENSE_RANK LAST ORDER BY DateFilled ) AS Position,
MAX( DateFilled ) AS DateFilled
FROM position
GROUP BY EmpID
) p
ON ( e.EmpId = p.EmpID );
Upvotes: 1
Reputation: 72235
You can use ROW_NUMBER
:
SELECT e.EmpID, e.Name, p.Position, p.DateFilled
FROM employee e
LEFT JOIN (
SELECT EmpID, Position, DateFilled,
ROW_NUMBER() OVER (PARTITION BY EmpID
ORDER BY DateFilled DESC) AS rn
FROM position
) p ON e.EmpID = p.EmpID AND p.rn = 1
Upvotes: 1