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

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
Solution

  • 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