I have a table given.

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

Desired output:

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?

CodePudding user response：

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;