Query Last data group by column

I have this data;

date        owner   p.code  product
----        -----   -----   ------
21.08.2020  Micheal   5    apple
22.08.2020  Micheal   5    apple
15.08.2020  George    4    biscuit
14.08.2020  George    4    biscuit
10.08.2020  Micheal   4    biscuit
23.08.2020  Alice     2    pear
15.08.2020  Alice     2    pear
14.08.2020  Micheal   2    pear
11.08.2020  Micheal   2    pear

I want to group them trought to product and show last date and last owner.

like this ;

date         owner  p.code  product
----         -----   ------  ------
22.08.2020  Micheal    5    apple
15.08.2020  George     4    biscuit
23.08.2020  Alice      2    pear

Answer

In Oracle, you can phrase this using group by:

select product, code,
       max(date) as max_date,
       max(owner) keep (dense_rank first order by date desc) as owner_at_max_date
from t
group by product, code;

The keep syntax is Oracle’s rather verbose way of implementing a first() aggregation function.

Advertisement

Answer