Oracle SQL Partition By to Select the Most Recent Date

Question

I am using the below SQL to select the top of stack most recent life event from my employee_life_events table. In order to pull top of stack (most recent) I needed to pull the highest le_seq_no for each life_event_date by ssn. So with the SQL below I have the top of stack life event record for each member (ssn) by date. I now need to pull the most recent date. Now I know that I could try to build another sub query and use a max function (not sure exactly how so tips on that would be appreciated), but I am also wondering if there is a more efficient way to do this with the partition by statement. By either adding a field to the partition by portion or by adding a field to the order by portion.

So to sum it all up, I need to pull the top of stack (most recent life event record) from the employee_life_events table by SSN. The most top of stack life event record is the record (per SSN) that is the most recent life_event_date, and has the highest le_seq_no (for that particular date).

SELECT *
FROM

(SELECT *
FROM
    (SELECT ele1.*,
        ROW_NUMBER() OVER (PARTITION BY ele1.SSN, LIFE_EVENT_DATE ORDER BY LIFE_EVENT_DATE DESC, LE_SEQ_NO DESC) as seqnum
    FROM EMPLOYEE_LIFE_EVENTS ele1) ele1
WHERE seqnum = 1) ele

Answer 1

The most top of stack life event record is the record (per SSN) that is the most recent life_event_date, and has the highest le_seq_no (for that particular date).

I think that you want:

select t.*
from (
    select 
        e.*,
        row_number() over(
            partition by ssn
            order by life_event_date desc, le_seq_no desc
        ) rn
    from employee_life_events e
) t
where rn = 1

This uses a different partition and sort than your original query. I also eliminated an unnecessary level of nesting.