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

Question

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    |
+------+--------------------------------+------------+--------+

Answer 1

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