hopefully someone has an idea for a more performant Oracle SQL.

Following Oracle PERSON-Table:

I want to have only the recent timestamps of identical REFERENCE_ID results from a specific PERSON_ID.

So I expect 3 rows should be in the result list for PERSON_ID 10066:

I came up with a subselect idea which works, but is possibly not the best / most performant solution:

SELECT * FROM PERSON_TABLE p WHERE CREATION_DATE = (
   SELECT MAX(CREATION_DATE)
   FROM PERSON_TABLE
   WHERE REFERENCE_ID = p.REFERENCE_ID AND PERSON_ID = 10066
);

Has someone a better idea? Or is my approach ok performance wise? I have the feeling there are more optimized statements / queries possible, maybe without subselect.

Thanks in advance, have a nice day!

CodePudding user response：

If I am understanding the question correctly I think a DISTINCT clause may achieve the same results.

Something like select distinct REFERENCE_ID, PERSON_ID, COMMENT, CREATION_DATE from PERSON_TABLE order by REFERENCE_ID desc.

CodePudding user response：

One option is to rank rows per each person and reference, and then fetch the ones that rank as the highest.

Sample data:

SQL> with person (id, person_id, reference_id, creation_date) as
  2    (select 1, 1066, 666, to_date('01.11.2022 09:16', 'dd.mm.yyyy hh24:mi') from dual union all
  3     select 2, 1066, 111, to_date('01.11.2022 11:44', 'dd.mm.yyyy hh24:mi') from dual union all
  4     select 3, 1066, 666, to_date('02.11.2022 07:37', 'dd.mm.yyyy hh24:mi') from dual union all
  5     select 5, 1066, 666, to_date('03.11.2022 08:46', 'dd.mm.yyyy hh24:mi') from dual union all
  6     select 6, 1066, 987, to_date('03.11.2022 09:02', 'dd.mm.yyyy hh24:mi') from dual union all
  7     select 7, 1066, 987, to_date('03.11.2022 09:44', 'dd.mm.yyyy hh24:mi') from dual
  8    ),

Query begins here:

  9  temp as
 10    (select p.*,
 11            row_number() over (partition by person_id, reference_id order by creation_date desc) rn
 12     from person p
 13    )
 14  select id, person_id, reference_id, creation_date
 15  from temp
 16  where person_id = 1066
 17    and rn = 1;

        ID  PERSON_ID REFERENCE_ID CREATION
---------- ---------- ------------ --------
         2       1066          111 01.11.22
         5       1066          666 03.11.22
         7       1066          987 03.11.22

SQL>