CODEWITHSUNDEEP
sqloracle-databasedatetimegreatest-n-per-group
Ömer Faruk
Ömer Faruk

Reputation: 111

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

Upvotes: 1

Views: 37

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 1

GMB
GMB

Reputation: 222462

You can use window functions:

select *
from (
    select t.*, row_number() over(partition by product order by date desc) rn 
    from mytable t
) t
where rn = 1

Upvotes: 1

Related Questions