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
| with cte_res (spof_grof_numero, spof_phof_numero_phase, qte) as
(
select 'G15-02409', 901, 1020 from dual union all
select 'G15-02408', 901, 372 from dual union all
select 'G15-02407', 901, 3240 from dual union all
select 'G15-02407', 906, 270 from dual union all
select 'G15-02407', 911, 270 from dual
)
, cte_int (spof_grof_numero, spof_phof_numero_phase, qte, nb_grof) as
(
select spof_grof_numero
, max(spof_phof_numero_phase)
, qte
, count(*) over(partition by spof_grof_numero) as nb_grof
from cte_res
group by spof_grof_numero, qte
)
select spof_grof_numero, spof_phof_numero_phase, qte
from cte_int
where nb_grof > 1
order by spof_grof_numero, spof_phof_numero_phase;
SPOF_GROF_NUMERO SPOF_PHOF_NUMERO_PHASE QTE
---------------- ---------------------- ----------
G15-02407 901 3240
G15-02407 911 270 |
Partager