CODEWITHSUNDEEP
sqloracle-database
ajm
ajm

Reputation: 13213

How to get the latest employee record in oracle?

I have a employee table with columns like emp_id, firstname, lastname, region_id, status and effective_date.

Employee Table can have multiple entries for same employee with different effective dates and statuses.

Employee can have two statuses 'Leaver' and 'Joiner'. 

id     emp_id    firstname     region    status     effective_date  
1       1         James        Asia      Joiner     1-Jan-2012 
2       1         James        UK        Leaver     1-Aug-2012
3       1         James        USA       Joiner     1-Aug-2012
4       1         James        Asia      Leaver     1-May-2012
5       1         James        UK        Joiner     1-May-2012
6       1         James        USA       Leaver     1-Sep-2012

With the above data in employee table, If i want to get the latest record of james as on 1 Jan 2012, I would get record with id = 1,

If i want to get the latest record of james as on 1 May 2012, I would get record with id = 5

If i want to get the latest record of james as on 1 Aug 2012, I would get record with id = 3,

If i want to get the latest record of james as on 1 Sep 2012, I would get record with id = 6

Following query correctly gives me latest record 

SELECT 
        emp_id, 
        MAX(effective_date) AS latest_effective_date
FROM 
        EMPLOYEE
GROUP BY 
        emp_id

But then how do I get the other columns such as firstname , region etc.

If I put them in select clause or group by clause, I dont just get the latest record but the other records as well.

Upvotes: 2

Views: 57796

Answers (6)

Anil
Anil

Reputation: 1

Problem : GET Employee records joined latest in the company

Solution :
Step 1. Get latest date when employees recently joined a company
Step 2. Get records of all employees who joined on that date

select * 
from EMPLOYEE 
where effective_date in (
    SELECT MAX(effective_date) AS latest_effective_date 
    FROM EMPLOYEE GROUP BY emp_id
);

Upvotes: 0

Null IO
Null IO

Reputation: 11

Try this one

SELECT  
  MAX(id) KEEP (DENSE_RANK FIRST ORDER BY effective_date DESC) id,
  MAX(emp_id) KEEP (DENSE_RANK FIRST ORDER BY effective_date DESC) emp_id,
  MAX(firstname) KEEP (DENSE_RANK FIRST ORDER BY effective_date DESC) firstname,
  MAX(status) KEEP (DENSE_RANK FIRST ORDER BY effective_date DESC) status,
  MAX(effective_date) KEEP (DENSE_RANK FIRST ORDER BY effective_date DESC) effective_date
FROM Employee GROUP BY firstname

Upvotes: 1

arturro
arturro

Reputation: 1606

SELECT * FROM 
( SELECT  
    e.*,
    ROW_NUMBER() OVER (partition by emp_id order by effective_date DESC) r
FROM  
    EMPLOYEE  e)
WHERE r = 1;

Above will get you a record with maximal effective__Date for every distinct emp_id.

Your second requirement of returning record for given date should be fullfiled by this query:

("status ASC" - will take care of taking "Joiner" status if there is also "Leaver" for the same date.)

 SELECT * FROM 
( SELECT  
    e.*,
    ROW_NUMBER() OVER (partition by emp_id order by effective_date DESC, status ASC) r
FROM  
    EMPLOYEE  e
WHERE effective_date <= '<your desired date>')
WHERE r=1;

Upvotes: 10

Euclides Mul&#233;mbw&#232;
Euclides Mul&#233;mbw&#232;

Reputation: 1687

the query you've entered doesn't necessarily return record with ids 3, 5, 6 like you stated before, because in this case:

2       1         James        Asia      Leaver     1-May-2012
3       1         James        UK        Joiner     1-May-2012

effective_date is equal for both rows and it would probably return record with id 2 and not 3.

try adding time to your table or adding time to your effective_date column, this way you'll be able to get the latest result from a user in a determined date.

Upvotes: 1

GarethD
GarethD

Reputation: 69769

You need to inner join the query you already have back to your Employee table to limit the records:

SELECT  Emp.*
FROM    Employee Emp
        INNER JOIN
        (   SELECT  Emp_ID, MAX(effective_date) AS latest_effective_date
            FROM    Employee
            GROUP BY Emp_ID
        ) MaxEmp
            ON Emp.Emp_ID = MaxEmp.Emp_ID
            AND Emp.Effective_Date = MaxEmp.latest_effective_date

Upvotes: 2

Euclides Mul&#233;mbw&#232;
Euclides Mul&#233;mbw&#232;

Reputation: 1687

try:

SELECT *
FROM EMPLOYEE emp
INNER JOIN (SELECT max(id) AS id
        emp_id, 
        MAX(effective_date) AS latest_effective_date
FROM 
        EMPLOYEE
GROUP BY 
        emp_id) AS employee_1 on emp.id = employee_1.id

Upvotes: 1

Related Questions