Search code examples
sqloracle-databasegreatest-n-per-group

Selecting last ID value of the same day

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?

Solution

  • 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;