data associated with max date

[Manon Baratt]

Hello,

I have a query that selects maximum effective end date from one of the tables I am querying. I want to list the effective start date for that particular row from the assignment table. I got a tip to use the analytics. The only problem is that this query returns multiple rows for a single job_ id and I want only the one with the latest effective_start_date. Here is the query:

select job_id, name, effective_start_date, max_date
from
(SELECT t.job_id,
j.name ,
t.effective_start_date ,
t.effective_end_date,
MAX(t.effective_end_date) over (partition by t.job_id, j.name) max_date
FROM assignments t,
jobs j
WHERE t.job_id IN
(SELECT j.job_id
FROM jobs j
WHERE sysdate BETWEEN j.date_from AND NVL(j.date_to, sysdate)
)
AND j.job_id = t.job_id(+))
where max_date = effective_end_date

Thanks for the help.