select DISTINCT
case
when KREG <> '05' then
row_number() over (partition by K_BRANCA
order by CASE
WHEN K_ASL like '05%' then 2
ELSE 1
END, K_ASL, ANNO,K_CODE )
when K_REGIME = '05' then 0
end NPROGRESS,
...
...
thanks in advance Rosario Vigilante wrote:Please explain it clearly. It's very confusing.
hello to all
how can I get from the following query, consecutive(no holes, no gap) n_progress when KREG = '05', even if it select both rows with '05' and rows <> '05 ',
that is, even if I have 10 rows of which 7 are different from 05 and 3 are equal '05 I have the numbers(n_progress)= 1,2,3,4,5,6,7
thanks in advanceselect DISTINCT case when KREG <> '05' then row_number() over (partition by K_BRANCA order by CASE WHEN K_ASL like '05%' then 2 ELSE 1 END, K_ASL, ANNO,K_CODE ) when K_REGIME = '05' then 0 end NPROGRESS, ... ...
kreg
05 ....
01 ....
01 ....
05 ....
01 ....
01 ....
05 ....
when I run the query, I can get something as
n_progress ...............
0 ...
1 ....
2 ....
0 ....
3 ....
4 ....
0 ....
I hope do is better now with t (id, kreg)
as (select 1,'05' from dual
union all select 2,'01' from dual
union all select 3,'01' from dual
union all select 4,'05' from dual
union all select 5,'01' from dual
union all select 6,'01' from dual
union all select 7,'05' from dual
)
select kreg
,case WHEN kreg = '05' then
0
else
row_number() over (partition by decode(kreg,'05',1,0) order by id)
end new_val
from t
order by id
/
KR NEW_VAL
__ __________
05 0
01 1
01 2
05 0
01 3
01 4
05 0
Rosario Vigilante wrote:Not really...
I hope do is better now
[11.2] Scott @ My11g > l
1 with t (grp, id)
2 as
3 (
4 select '05', 1 from dual union all
5 select '05', 4 from dual union all
6 select '05x', 7 from dual union all
7 select '07', 2 from dual union all
8 select '07z', 9 from dual
9 )
10 select grp, id, row_number() over (partition by substr(grp,1,2) order by id) rn
11* from t
[11.2] Scott @ My11g > /
GRP ID RN
--- ---------- ----------
05 1 1
05 4 2
05x 7 3
07 2 1
07z 9 2
with data as (
select '05' kreg , 1 id from dual union all
select '01', 2 from dual union all
select '01', 3 from dual union all
select '05', 4 from dual union all
select '01', 5 from dual union all
select '01', 6 from dual union all
select '05', 7 from dual
)
select
case kreg
when '05' then 0
else
id - sum(decode(kreg, '05',1,0)) over (order by id)
end n
,kreg
,id
from data
N KREG ID
0 05 1
1 01 2
2 01 3
0 05 4
3 01 5
4 01 6
0 05 7
CASE serviceambula
WHEN 'N' THEN
CASE
WHEN O.K_REGIME <> '05' THEN 1
WHEN O.K_REGIME = '05' THEN 0
END
WHEN 'S' THEN
CASE
WHEN O.K_REGIME <> '05' THEN 0
WHEN O.K_REGIME = '05' THEN 1
END
END > 0
Thanks againwith xx as(
select '05' kreg from dual union all
select '01' kreg from dual union all
select '01' kreg from dual union all
select '05' kreg from dual union all
select '01' kreg from dual union all
select '01' kreg from dual union all
select '05' kreg from dual
)
select kreg,
case when kreg = '05'
then 0
else
row_number() over (partition by decode(kreg,'05',0,1) order by kreg)
end case
from (select kreg from xx order by kreg) xx;
I'm still confused... what is K_REGIME ??select DISTINCT case when KREG <> '05' then row_number() over (partition by K_BRANCA order by CASE WHEN K_ASL like '05%' then 2 ELSE 1 END, K_ASL, ANNO,K_CODE ) when K_REGIME = '05' then 0 end NPROGRESS, ... ...