Oracle 11g Release 2

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

CREATE TABLE "CAR_EQUIPMENT" 
   (    "COUNT_CARS" NUMBER(10,0), 
"EQUIPMENT_TYPE" VARCHAR2(100 BYTE), 
"LOCATION" VARCHAR2(500 BYTE), 
"INVENTORY_DATE" DATE) ;

INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION,     INVENTORY_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2019-09-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
1 rows affected

INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('9', 'Jaguars', 'coventry', TO_DATE('2019-09-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
1 rows affected

INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('7', 'Rovers', 'leamington', TO_DATE('2019-08-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
1 rows affected

INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('10','Trans Am', 'leamington', TO_DATE('2019-08-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
1 rows affected

INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2019-01-07 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
1 rows affected

INSERT INTO "CAR_EQUIPMENT" (COUNT_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('4', 'Rovers', 'leamington', TO_DATE('2019-01-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
1 rows affected

SELECT * FROM CAR_EQUIPMENT


COUNT_CARS
EQUIPMENT_TYPE
LOCATION
INVENTORY_DATE


8
Rovers
coventry
07-SEP-19

9
Jaguars
coventry
07-SEP-19

7
Rovers
leamington
30-AUG-19

10
Trans Am
leamington
30-AUG-19

8
Rovers
coventry
07-JAN-19

4
Rovers
leamington
08-JAN-19


SELECT 
    inventory_date max_inventory_date,
    location,
    LISTAGG(count_cars || ' ' || equipment_type, ', ') WITHIN GROUP (ORDER BY equipment_type) inventory_list
FROM (
    SELECT c.*, RANK() OVER(PARTITION BY location ORDER BY inventory_date DESC) rn
    FROM car_equipment c
) x 
WHERE rn = 1
GROUP BY inventory_date, location;


MAX_INVENTORY_DATE
LOCATION
INVENTORY_LIST


30-AUG-19
leamington
7 Rovers, 10 Trans Am

07-SEP-19
coventry
9 Jaguars, 8 Rovers

COUNT_CARS	EQUIPMENT_TYPE	LOCATION	INVENTORY_DATE
8	Rovers	coventry	07-SEP-19
9	Jaguars	coventry	07-SEP-19
7	Rovers	leamington	30-AUG-19
10	Trans Am	leamington	30-AUG-19
8	Rovers	coventry	07-JAN-19
4	Rovers	leamington	08-JAN-19

MAX_INVENTORY_DATE	LOCATION	INVENTORY_LIST
30-AUG-19	leamington	7 Rovers, 10 Trans Am
07-SEP-19	coventry	9 Jaguars, 8 Rovers