By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
e_id NUMBER(10),
f_name VARCHAR2(50),
created_on TIMESTAMP,
created_by VARCHAR2(50)
);
INSERT INTO test VALUES(11,'a',to_date('18-07-22 12:06:19', 'dd-mm-yyyy hh24:mi:ss'),'aa');
1 rows affected
INSERT INTO test VALUES(11,'a',to_date('18-07-22 12:06:18', 'dd-mm-yyyy hh24:mi:ss'),'aa');
1 rows affected
INSERT INTO test VALUES(11,'b',to_date('18-07-22 11:06:19', 'dd-mm-yyyy hh24:mi:ss'),'bb');
1 rows affected
INSERT INTO test VALUES(11,'c',to_date('16-07-22 12:06:19', 'dd-mm-yyyy hh24:mi:ss'),'cc');
1 rows affected
INSERT INTO test VALUES(11,'a',to_date('15-07-22 12:06:19', 'dd-mm-yyyy hh24:mi:ss'),'dd');
1 rows affected
INSERT INTO test VALUES(11,'a',to_date('12-07-22 12:06:19', 'dd-mm-yyyy hh24:mi:ss'),'ee');
1 rows affected
INSERT INTO test VALUES(11,'a',to_date('11-07-22 12:06:19', 'dd-mm-yyyy hh24:mi:ss'),'ff');
1 rows affected
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
| E_ID | CREATED_ON | CREATED_BY | LISTAGG(CTE.F_NAME,';')WITHINGROUP(ORDERBYCTE.F_NAME) |
|---|---|---|---|
| 11 | 18-JUL-22 12.06.19.000000 | aa | a;b |