Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t
    (eventDate date, Car_id int, licensePlate varchar(9), position int, scrapedAt int)
;
    
INSERT INTO t
    (eventDate, Car_id, licensePlate, position, scrapedAt)
VALUES
    ('2022-09-10 00:00:00', 1, 'APRJSC512', 1, 1660000001),
    ('2022-09-10 00:00:00', 1, 'APRJSC512', 1, 1660000002),
    ('2022-09-10 00:00:00', 1, 'PLBQWN035', 1, 1660000003),
    ('2022-09-10 00:00:00', 1, 'PLBQWN035', 1, 1660000004),
    ('2022-09-10 00:00:00', 1, 'PLBQWN035', 2, 1660000002),
    ('2022-09-11 00:00:00', 2, 'APRJSC512', 1, 1660000011),
    ('2022-09-11 00:00:00', 2, 'APRJSC512', 2, 1660000022),
    ('2022-09-11 00:00:00', 2, 'PLBQWN035', 1, 1660000033),
    ('2022-09-11 00:00:00', 2, 'PLBQWN035', 2, 1660000044),
    ('2022-09-11 00:00:00', 2, 'PLBQWN035', 5, 1660000022),
    ('2022-09-12 00:00:00', 3, 'APRJSC512', 3, 1660000111),
    ('2022-09-12 00:00:00', 3, 'PLBQWN035', 3, 1660000222),
    ('2022-09-13 00:00:00', 4, 'PLBQWN035', 4, 1660001111),
    ('2022-09-14 00:00:00', 5, 'PLBQWN035', 5, 1660011111)
;

CREATE TABLE



INSERT 0 14


select *
from   t


eventdate
car_id
licenseplate
position
scrapedat


2022-09-10
1
APRJSC512
1
1660000001

2022-09-10
1
APRJSC512
1
1660000002

2022-09-10
1
PLBQWN035
1
1660000003

2022-09-10
1
PLBQWN035
1
1660000004

2022-09-10
1
PLBQWN035
2
1660000002

2022-09-11
2
APRJSC512
1
1660000011

2022-09-11
2
APRJSC512
2
1660000022

2022-09-11
2
PLBQWN035
1
1660000033

2022-09-11
2
PLBQWN035
2
1660000044

2022-09-11
2
PLBQWN035
5
1660000022

2022-09-12
3
APRJSC512
3
1660000111

2022-09-12
3
PLBQWN035
3
1660000222

2022-09-13
4
PLBQWN035
4
1660001111

2022-09-14
5
PLBQWN035
5
1660011111



SELECT 14


select  eventDate
       ,Car_id 
       ,licensePlate 
       ,position 
       ,scrapedAt
from
(
select  *
       ,row_number() over(partition by car_id order by position, scrapedat desc) as rn
from    t
) t
where   rn = 1


eventdate
car_id
licenseplate
position
scrapedat


2022-09-10
1
PLBQWN035
1
1660000004

2022-09-11
2
PLBQWN035
1
1660000033

2022-09-12
3
PLBQWN035
3
1660000222

2022-09-13
4
PLBQWN035
4
1660001111

2022-09-14
5
PLBQWN035
5
1660011111



SELECT 5

eventdate	car_id	licenseplate	position	scrapedat
2022-09-10	1	APRJSC512	1	1660000001
2022-09-10	1	APRJSC512	1	1660000002
2022-09-10	1	PLBQWN035	1	1660000003
2022-09-10	1	PLBQWN035	1	1660000004
2022-09-10	1	PLBQWN035	2	1660000002
2022-09-11	2	APRJSC512	1	1660000011
2022-09-11	2	APRJSC512	2	1660000022
2022-09-11	2	PLBQWN035	1	1660000033
2022-09-11	2	PLBQWN035	2	1660000044
2022-09-11	2	PLBQWN035	5	1660000022
2022-09-12	3	APRJSC512	3	1660000111
2022-09-12	3	PLBQWN035	3	1660000222
2022-09-13	4	PLBQWN035	4	1660001111
2022-09-14	5	PLBQWN035	5	1660011111