Oracle optimise SQL query - Multiple Max()

Question

I have a table where first I need to select data by max(event_date) then need to filter the data by max(event_sequence) then filter again by max(event_number)

I wrote following query which works but takes time.

Here the the query

SELECT DISTINCT a.stuid,
                   a.prog,
                   a.stu_prog_id,
                   a.event_number,
                   a.event_date,
                   a.event_sequence,
                   a.prog_status
   FROM table1 a
   WHERE a.event_date=
       (SELECT max(b.event_date)
        FROM table1 b
        WHERE a.stuid=b.stuid
          AND a.prog=b.prog
          AND a.stu_prog_id=b.stu_prog_id)
     AND a.event_seq=
       (SELECT max(b.event_sequence)
        FROM table1 b
        WHERE a.stuid=b.stuid
          AND a.prog=b.prog
          AND a.stu_prog_id=b.stu_prog_id
          AND a.event_date=b.event_date)
     AND a.event_number=
       (SELECT max(b.event_number)
        FROM table1 b
        WHERE a.stuid=b.stuid
          AND a.prog=b.prog
          AND a.stu_prog_id=b.stu_prog_id
          AND a.event_date=b.event_date
          AND a.event_sequence=b.event_sequence

I was wondering is there there a faster way to get the data? I am using Oracle 12c.

Answer 1

I think you want a simple row_number() or rank():

select t1.*
from (select t1.*,
             rank() over (partition by stuid, prog, stu_prog_id
                          order by event_date desc, event_sequence desc, event_number desc
                         ) as seqnum
      from table1 t1
     ) t1
where seqnum = 1;