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

Select car of max(date) for every employee

I would need a code for the following problem:

I have a table like this:

Employee Year Month Car
Tom 2021 9 Ford
Tom 2021 10 Ford
Tom 2021 11 Ford
Tom 2021 12 Renault
Tom 2022 1 Renault
Mark 2021 12 VW
Mark 2022 1 VW
Mark 2022 2 VW
Joe 2021 8 Opel
Joe 2021 9 Tesla
Joe 2021 10 Ferrari

And I would need the car used by the employee for the last possible date. So the result should be:

Employee Car
Tom Renault
Mark VW
Joe Ferrari

With:

select employee, max(year || month) from table.cars
group by employee

I get the max(date) for every employee, but I do not know how to join the cars to the max(date).

How can I get the result I want?

Solution 
  • with t as
(
  select *,
        row_number() over (partition by employee order by year desc, month desc) rn
  from cars 
) 
select employee, car 
from t
where rn = 1