How to select maximum records from timestamp and do the operation using LISTAGG-CodePudding

CREATE TABLE test (
    e_id        NUMBER(10),
    f_name      VARCHAR2(50),
    created_on  TIMESTAMP,
    created_by  VARCHAR2(50)
);

INSERT INTO test VALUES(11,'a','18-07-22 12:06:19.566000000 PM','aa');
INSERT INTO test VALUES(11,'b','18-07-22 11:06:19.566000000 PM','bb');
INSERT INTO test VALUES(11,'c','16-07-22 12:06:19.566000000 PM','cc');
INSERT INTO test VALUES(11,'a','15-07-22 12:06:19.566000000 PM','dd');
INSERT INTO test VALUES(11,'a','12-07-22 12:06:19.566000000 PM','ee');
INSERT INTO test VALUES(11,'a','11-07-22 12:06:19.566000000 PM','ff');

DB version: Oracle 18c

I need to populate maximum created_on, created_by````, and f_name``` from the following logic:

created_on: MAX(created_on)

created_by: MAX(created_by)

f_name: First need to find out the maximum created_on in our dataset it would be '18-07-22 12:06:19.566000000 PM' and '18-07-22 11:06:19.566000000 PM' and need to populate f_name column value in delimiter separated i.e a;b for which I will use LISTAGG.

My Attempt:

WITH a AS(SELECT e_id, MAX(created_on)created_on,MAX(created_by)created_by
FROM test),
b AS(
SELECT e_id, LISTAGG(DISTINCT(f_name))
FROM test WHERE created_on = --need to select MAX created_on for particular date
);

Need to use DISTINCT in LISTAGG because there may be same records present for an e_id

Expected output:

 ------ -------------------------------- ------------ -------- 
| e_id |           created_on           | created_by | f_name |
 ------ -------------------------------- ------------ -------- 
|   11 | 18-07-22 12:06:19.566000000 PM | aa         | a;b    |
 ------ -------------------------------- ------------ --------

CodePudding user response：

You can RANK the rows by created_on and then filter to only include those rows with the maximum rank:

SELECT e_id,
       MAX(created_on),
       LISTAGG(created_by, ';') WITHIN GROUP (ORDER BY f_name)
         AS created_by,
       LISTAGG(f_name, ';') WITHIN GROUP (ORDER BY f_name)
         AS f_names
FROM   (
  SELECT DISTINCT *
  FROM   (
    SELECT t.*,
           RANK() OVER (PARTITION BY e_id ORDER BY created_on DESC) AS rnk
    FROM   test t
  )
  WHERE rnk = 1
)
GROUP BY e_id

Which, for the sample data:

CREATE TABLE test (e_id, f_name, created_on, created_by) AS
SELECT 11,'a', TIMESTAMP '2022-07-18 12:06:19.566000000','aa' FROM DUAL UNION ALL
SELECT 11,'b', TIMESTAMP '2022-07-18 23:06:19.566000000','bb' FROM DUAL UNION ALL
SELECT 11,'c', TIMESTAMP '2022-07-16 12:06:19.566000000','cc' FROM DUAL UNION ALL
SELECT 11,'a', TIMESTAMP '2022-07-15 12:06:19.566000000','dd' FROM DUAL UNION ALL
SELECT 11,'a', TIMESTAMP '2022-07-12 12:06:19.566000000','ee' FROM DUAL UNION ALL
SELECT 11,'a', TIMESTAMP '2022-07-11 12:06:19.566000000','ff' FROM DUAL;

Outputs:

E_ID MAX(CREATED_ON) CREATED_BY F_NAMES

11 18-JUL-22 23.06.19.566000000 bb b

E_ID	MAX(CREATED_ON)	CREATED_BY	F_NAMES
11	18-JUL-22 23.06.19.566000000	bb	b

Note: a is not included as it is not the maximum.

If you want the rows with the maximum date (but not time component) then:

SELECT e_id,
       MAX(created_on),
       LISTAGG(created_by, ';') WITHIN GROUP (ORDER BY f_name)
         AS created_by,
       LISTAGG(f_name, ';') WITHIN GROUP (ORDER BY f_name)
         AS f_names
FROM   (
  SELECT DISTINCT *
  FROM   (
    SELECT t.*,
           RANK() OVER (PARTITION BY e_id ORDER BY TRUNC(created_on) DESC) AS rnk
    FROM   test t
  )
  WHERE rnk = 1
)
GROUP BY e_id

Which outputs:

E_ID MAX(CREATED_ON) CREATED_BY F_NAMES

11 18-JUL-22 23.06.19.566000000 aa;bb a;b

E_ID	MAX(CREATED_ON)	CREATED_BY	F_NAMES
11	18-JUL-22 23.06.19.566000000	aa;bb	a;b

db<>fiddle here

CodePudding user response：

I have added one more row to the demo table to demonstrate situation you mentioned: "Need to use DISTINCT in LISTAGG because there may be same records present for an e_id".

I do not see why you need something like this but here is my try to do it:

with cte as (select distinct e_id, f_name, trunc(created_on) created_on, created_by 
             from test)
, cte2 as (select e_id, created_on, created_by 
           from test 
           where created_on in (select max(created_on) from test))
select cte.e_id
       , cte2.created_on
       , cte2.created_by
       , listagg(cte.f_name, ';') within group (order by cte.f_name)
from cte
join cte2 on cte.e_id = cte2.e_id and cte.created_on = trunc(cte2.created_on)
group by cte.e_id
       , cte2.created_by
       , cte2.created_on

DEMO