1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
|
create table t_p
(
blocNote varchar2(2),
ma_date date,
affaire Number(6)
)
/
insert into t_p values('aa','01/02/2007',144000);
insert into t_p values('bb','01/03/2007',144000);
insert into t_p values('cc','19/11/2007',144000);
insert into t_p values('cc','19/11/2007',144001);
insert into t_p values('dd','19/11/2007',144001);
insert into t_p values('ee','10/12/2007',144001);
/
SELECT SUBSTR (SYS_CONNECT_BY_PATH (blocnote, ' '), 2) name_list, ma_date
FROM (SELECT blocnote, affaire, COUNT (*) OVER (PARTITION BY affaire) cnt,
MAX (ma_date) OVER (PARTITION BY affaire) ma_date,
ROWNUM seq
FROM t_p
WHERE blocnote IS NOT NULL)
WHERE seq = cnt
START WITH seq = 1
CONNECT BY PRIOR seq + 1 = seq AND PRIOR affaire = affaire
/
NAME_LIST
-----------------------------------------
MA_DATE
--------
aa bb cc
19/11/07 |
Partager