CODEWITHSUNDEEP
sqloracle-database
Muthukumar
Muthukumar

Reputation: 113

Get the last update time for each employee using oracle query

I have tried the below query to get the last update value.. But I can't get single row for each employee

select distinct Employee_name,Employee_status,update_time
  from Employee 
 where Employee_name= 'Muthukumar'
   and Employee_status='ACTIVE' 
 order by Employee_name, Update_time desc

Employee_name       Employee_status  Update_time
Muthukumar          ACTIVE           06-DEC-12 10.18.59.048000000 AM
Muthukumar          ACTIVE           05-DEC-12 05.05.41.165000000 AM
Muthukumar          ACTIVE           04-SEP-12 03.00.54.706000000 AM

Like this i will have entries for different employees. I need to get a single row for each employee with only last updated time.( Highest)

Output should be :

Employee_name  Employee_status   Update_time
Muthukumar     ACTIVE            06-DEC-12 10.18.59.048000000 AM

Upvotes: 0

Views: 414

Answers (2)

Cyril Gandon
Cyril Gandon

Reputation: 17058

It is a classic analytic rank request. You need to rank all your rows by partitioning them by employees and order them by date. Then take only the first top rank.

Some reading here and here.

SELECT Employee_name, Employee_status, update_time  
FROM
(
    SELECT Employee_name, Employee_status, update_time,
           RANK() OVER (PARTITION BY Employee_name ORDER BY update_time DESC) as Rank
    FROM Employee
)
WHERE Rank = 1

Upvotes: 0

John Woo
John Woo

Reputation: 263843

Oracle supports common table expression and window function. With the help of using ROW_NUMBER() it ranks each record of the group based on a certain order.

WITH empList
AS
(
    SELECT  Employee_Name,
            Employee_Status,
            Update_Time,
            ROW_NUMBER() OVER(PARTITION BY Employee_Name
                                ORDER BY Update_Time DESC) rn
    FROM    EmployeeList
)
SELECT  Employee_Name,
        Employee_Status,
        Update_Time
FROM    empList
WHERE   rn = 1

Upvotes: 1

Related Questions