CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Audina Tira
Audina Tira

Reputation: 1

Sub query max date

I have 5 tables: employee, person_analys, criteria, assignment, and pay

I want to show : 

employee_id and full_name from employee,
date_from and date_to from person_analys,
segment1 and segment2 from criteria,
efective_end_date from assignment,
salary from pay

I want to show every employee_id and only show 1 row in 1 day and take the last date (max date from effective_end_date). date_from and date_to is describe for day.

Example : 

employee_id    full_name    date_from    date_to     segment1    segment2    effective_end_date    salary
11111        aaa        01-Jan-17    01-Jan-17    11:00        17:00        02-Jan-17        1000
11111        aaa        01-Jan-17    01-Jan-17    11:00        17:00        01-Jan-17        2000

I want to show only 1 row each employee and each day based on max effective end date like this : 

employee_id    full_name    date_from    date_to     segment1    segment2    effective_end_date    salary
11111        aaa        01-Jan-17    01-Jan-17    11:00        17:00        02-Jan-17        1000

please help me. I have tried but the where condition is not working and still show all data. thank you :)

Upvotes: 0

Views: 127

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

Use subquery behind FROM

with join_of_tables as (
  select TO_DATE(effective_end_date as date, 'DD-MON-YY') as effective_end_date, ...
  -- here you should have a join of your tables
)
select e.*
from join_of_tables e
join (
    select employee_id, max(effective_end_date) max_effective_end_date   
    from join_of_tables
    group by employee_id
) t on e.employee_id= t.employee_id and
       e.effective_end_date = t.max_effective_end_date

If the datatype of effective_end_date is varchar2 then you should cast it to date first (TO_DATE function in my example).

Upvotes: 1

Related Questions