Oracle SQL: receive ID of grouped foreign key with smallest Date

Admin 1
Viewed 31

I have a table given.

I need the ID of each BID with the smallest MODIFIED date

ID BID MODIFIED
1 1 01.01.2020
2 1 01.07.2020
3 2 04.08.2020
4 2 04.06.2020
5 2 01.07.2020
6 2 01.10.2020
7 3 01.09.2020

Desired output:

ID BID MODIFIED
1 1 01.01.2020
4 2 04.06.2020
7 3 01.09.2020

so far, I can get a list of BIDs with the smallest MODIFIED date, but not the ID from it:

select BID, min(MODIFIED) from MY_TABLE group by BID

how can I receive the ID, however?

oracle sql
1 Answers

Oracle has a "first" aggregation function, which uses the keep syntax:

select BID, min(MODIFIED),
       min(id) keep (dense_rank first over order by modified) as id
from MY_TABLE
group by BID;

A common alternative uses window functions:

select t.*
from (select t.*,
             row_number() over (partition by bid order by modified asc) as seqnum
      from my_table t
     ) t
where seqnum = 1;
user_11440350
Related