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;

You need to apply a GROUP BY clause. ORDER BY will not group the data, but just sort it. I guess, something like GROUP BY TRUNC(timestamp) will do in your case. — Jonas Metzler, Oct 28 '22 at 10:33

score 2 · Accepted Answer · answered Oct 28 '22 at 10:40

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>

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

1 Answers1