SQL query to Select max timestamp, id, etc of each day in oracle

Question

I just tried to retrieve max record of each day based on timestamp. For example:

Dummy date

id    type     timestamp           , etc
1      a       07/10/2022 12:54:59
2      a       07/10/2022 12:50:59
3      b       05/10/2022 12:54:59
4      c       05/10/2022 10:54:59
5      d       01/09/2022 12:54:59
6      c       01/09/2022 12:54:50

Expected result

id    type     timestamp           , etc
1      a       07/10/2022 12:54:59
3      b       05/10/2022 12:54:59
5      d       01/09/2022 12:54:59

I have written below SQL query but it's not working as expected:

select c.code, to_char (p.TIMESTAMP, 'DD/MM/YYYY HH24:MI:ss') as time_stamp, p.TYPE1
from table1 p
INNER JOIN table2 c on c.id3 =p.id2
where p.id1= 1234
and p.id2 = 1
and p.type1 = 'X'
and c.CODE = 'XYZ'
and to_char (p.TIMESTAMP, 'DD/MM/YYYY') between '01/05/2011' and '30/05/2011' 
order by c.code, p.id desc;

Answer 1

One option is to rank rows per each date, sorted by exact timestamp value in descending order (so that rows you're interested in rank as the highest), and then retrieve only these rows.

Sample data:

SQL> with table1 (id, type, timestamp) as
  2    (select 1, 'a', to_date('07.10.2022 12:54:59', 'dd.mm.yyyy hh24:mi:ss') from dual union all
  3     select 2, 'a', to_date('07.10.2022 12:50:59', 'dd.mm.yyyy hh24:mi:ss') from dual union all
  4     select 3, 'b', to_date('05.10.2022 12:54:59', 'dd.mm.yyyy hh24:mi:ss') from dual union all
  5     select 4, 'c', to_date('05.10.2022 10:54:59', 'dd.mm.yyyy hh24:mi:ss') from dual
  6    ),

Query begins here:

  7  temp as
  8    (select id, type, timestamp,
  9       rank() over (partition by trunc(timestamp) order by timestamp desc) rn
 10     from table1
 11    )
 12  select id, type, timestamp
 13  from temp
 14  where rn = 1
 15  order by id;

        ID T TIMESTAMP
---------- - -------------------
         1 a 07.10.2022 12:54:59
         3 b 05.10.2022 12:54:59

SQL>