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 35 36 37 38 39 40 41 42
| with ptar as
(
select 'TOTO' as nomptar, 123456 as x_ptar, 369852 as y_ptar, 1 as idlig from dual union all
select 'TOTO' , 456789 , 258711 , 2 from dual union all
select 'TOTO' , 7898123 , 147852 , 3 from dual union all
select 'TOTO' , 456123 , 258963 , 4 from dual union all
select 'TOTO' , 789456 , 789123 , 5 from dual
), ligne as
(
select 1 as idlig, 1 as idres from dual union all
select 2 , 1 from dual union all
select 3 , 2 from dual union all
select 4 , 2 from dual union all
select 5 , 3 from dual
)
select sr.nomptar,
sr.x_ptar,
sr.y_ptar,
sr.idres
from
(
select
p.nomptar,
p.x_ptar,
p.y_ptar,
l.idres,
row_number() over(partition by p.nomptar, l.idres order by null) as rk
from
ptar p
inner join ligne l
on l.idlig = p.idlig
where
l.idres < 4
) sr
where sr.rk = 1
order by sr.nomptar ASC,
sr.idres ASC;
NOMPTAR X_PTAR Y_PTAR IDRES
TOTO 123456 369852 1
TOTO 7898123 147852 2
TOTO 789456 789123 3 |
Partager