I have a table like this:

I want to write a query that groups by the division and region columns and gives me the last 5 dates for each group separated by commas in a single column. Something like this:

The last two columns are custom calculated columns that I also need for the final output table. Any help would be greatly appreciated as I have tried a lot of things but I keep getting errors about it not being grouped properly, possibly because of the two extra columns at the end. But even without that, I am not sure how to fetch only the last 5 dates per group in oracle.

Thanks!

CodePudding user response：

You want to filter the greatest-n-per-group using the ROW_NUMBER analytic function and then aggregate:

SELECT division,
       region,
       LISTAGG(TO_CHAR(date_of_last_visit, 'DD/MM/YYYY'), ',')
         WITHIN GROUP (ORDER BY date_of_last_visit DESC)
         AS date_of_last_visit,
       SYSDATE AS today,
       TRUNC(SYSDATE - MAX(date_of_last_visit)) AS days_since_last_visit
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY division, region
                            ORDER BY date_of_last_visit DESC) AS rn
  FROM   table_name t
)
WHERE  rn <= 5
GROUP BY
       division,
       region

Which, for the sample data:

CREATE TABLE table_name (division, region, date_of_last_visit) as
select 1, 2, date '2021-11-20' from dual union all
select 1, 2, date '2021-11-18' from dual union all
select 1, 7, date '2021-10-18' from dual union all
select 1, 7, date '2021-11-19' from dual union all
select 2, 2, date '2021-11-17' from dual union all
select 2, 3, date '2021-09-20' from dual union all
select 2, 3, date '2021-10-20' from dual;

Outputs:

DIVISION	REGION	DATE_OF_LAST_VISIT	TODAY	DAYS_SINCE_LAST_VISIT
1	2	20/11/2021,18/11/2021	30-NOV-21	10
1	7	19/11/2021,18/10/2021	30-NOV-21	11
2	2	17/11/2021	30-NOV-21	13
2	3	20/10/2021,20/09/2021	30-NOV-21	41

db<>fiddle here

CodePudding user response：

Here you go; read comments within code.

SQL> with test (division, region, datum) as
  2    -- sample data
  3    (select 1, 2, date '2021-11-20' from dual union all
  4     select 1, 2, date '2021-11-18' from dual union all
  5     select 1, 7, date '2021-10-18' from dual union all
  6     select 1, 7, date '2021-11-19' from dual union all
  7     select 2, 2, date '2021-11-17' from dual union all
  8     select 2, 3, date '2021-09-20' from dual union all
  9     select 2, 3, date '2021-10-20' from dual
 10    ),

 11  temp as
 12    -- rank rows per division/region, sorted by date column in descending order
 13    (select t.*,
 14       rank() over (partition by division, region order by datum desc) rnk
 15     from test t
 16    )
 17  -- select up to 5 last rows per division/region
 18  select division, region,
 19    listagg(datum, ', ') within group (order by datum) dates,
 20    trunc(sysdate) today,
 21    --
 22    (select trunc(sysdate) - a.datum
 23     from temp a
 24     where a.division = t.division
 25       and a.region = t.region
 26       and a.rnk = 1) days_since
 27  from temp t
 28  where rnk <= 5
 29  group by division, region
 30  order by division, region;

  DIVISION     REGION DATES                          TODAY      DAYS_SINCE
---------- ---------- ------------------------------ ---------- ----------
         1          2 11/18/2021, 11/20/2021         11/30/2021         10
         1          7 10/18/2021, 11/19/2021         11/30/2021         11
         2          2 11/17/2021                     11/30/2021         13
         2          3 09/20/2021, 10/20/2021         11/30/2021         41

SQL>