CODEWITHSUNDEEP
sqloracle-databasejoingreatest-n-per-group
user3772397
user3772397

Reputation: 113

Retrieving MAX(date) value from joined table

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

Answers (3)

kedar kamthe
kedar kamthe

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

MT0
MT0

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

Giorgos Betsos
Giorgos Betsos

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

Related Questions