Selecting last ID value of the same day

Question

I have a sample table as you can see below

ID	Date	Info
1	15.02.2020	a
2	15.02.2020	b
1	15.02.2020	c
1	15.02.2020	d
3	15.02.2020	e
1	16.02.2020	f
3	16.02.2020	g
3	16.02.2020	h

I need to create a select statement that show me the last row of every ID in the same day. As you can see below.

ID	Date	Info
2	15.02.2020	b
1	15.02.2020	d
3	15.02.2020	e
1	16.02.2020	f
3	16.02.2020	h

How can I manage it in Oracle SQL?

Answer 1

One method uses a correlated subquery:

select t.*
from t
where t.id = (select max(t2.id)
              from t t2
              where t2.info = t.info and t2.date = t.date
             );

You can also use window functions:

select t.*
from (select t.*,
             row_number() over (partition by info, date order by id desc) as seqnum
      from t
     ) t
where seqnum = 1;