How to derive max date on LISTAGG Oracle 12c

debugcn 投稿 Dev

Gilly

Oracle 12c installation. I am having trouble organizing Oracle listagg() to show the count of cars column of my query to display the results of only the max(inventory_date) data per location.

I was thinking to create a view to aggregate the results:

create or replace view car_inventory_vw as 
select max(a.inventory_date) as max_date,
a.location,LISTAGG(a.COUNT_CARS||' '||a.EQUIPMENT_TYPE,',')
within GROUP (ORDER BY a.equipment_type) as equipment_list 
from car_equipment a 
group by a.location;

Create the table and insert test rows, Installed on Oracle 12c on Windows

CREATE TABLE "CAR_EQUIPMENT" 
   (    "COUNT_CARS" NUMBER(10,0), 
"EQUIPMENT_TYPE" VARCHAR2(100 BYTE), 
"LOCATION" VARCHAR2(500 BYTE), 
"INVENTORY_DATE" DATE) 
SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "USERS" ;

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'));
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'));
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'));
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'));
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'));
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'));

Results should be the inventory list of the max(inventory_date) by location:

MAX(INVENTORY_DATE) LOCATION    INVENTORY_LIST
-------------------- ----------- -------------------------
2019-08-30          leamington  10 Trans AM, 7 Rovers
2019-09-07          coventry    8 Rovers, 9 Jaguars

I am stuck on a simple problem, please advise how I can get the list created for the max date data only by location.

Thank you in advance, Gilly

GMB

You could proceed in two steps :

first, use RANK() in a subquery to identify the records that correspond to the latest inventory date at each location
then, use aggregation in the outer query and generate the inventory list of each location using LISTAGG()

Query:

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;

Demo on DB Fiddle

Data:

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

Results:

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

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]