Select car of max(date) for every employee

Question

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?

What is the type of Year, Month columns? Sample data look like char. — Serg, Feb 04 '22 at 07:13
You can check out the [demo](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=e75ed2026701909e2df7193616b5d45b) — Barbaros Özhan, Feb 04 '22 at 10:40

Barbaros Özhan · Answer 1 · 2022-02-04T07:16:53.107

You can use ROW_NUMBER() analytic function such as

SELECT Employee, Car
  FROM (SELECT ROW_NUMBER() OVER 
                           (PARTITION BY Employee ORDER BY year DESC, month DESC) AS rn,
               c.*
          FROM cars c)
 WHERE rn = 1

provided that the data type of the year and month are of string type, then you can replace the part ORDER BY year DESC, month DESC with

ORDER BY TO_NUMBER(TRIM(year)) DESC, TO_NUMBER(TRIM(month)) DESC

juergen d · Accepted Answer · 2022-02-04T07:47:27.710

-1

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

edited Feb 04 '22 at 07:47

answered Feb 04 '22 at 07:11

juergen d

201,996
37
293
362

and `as` keyword is missing too if you notice – Barbaros Özhan Feb 04 '22 at 07:30

score -1 · Answer 3 · answered Feb 04 '22 at 07:17

-1

Try this:

select employee, car 
from ( 
        Select *, ROW_NUMBER(partition by employee order by year, month DESC) as row_number
        from cars
        )a
Where row_number = 1

answered Feb 04 '22 at 07:17

Shu Rahman

634
1
5
15

Select car of max(date) for every employee

3 Answers3