12 Replies Latest reply on Nov 23, 2012 9:56 AM by Rosario Vigilante

# no gap in rownumber

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

``````    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,
...
...``````
• ###### 1. Re: no gap in rownumber
Hi,

I imagine you could use substr (or trim maybe), but as you don't give us data to play with, and don't tell what output you want, it's hard to tell...
• ###### 2. Re: no gap in rownumber
Rosario Vigilante wrote:
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

``````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,
...
...``````
Please explain it clearly. It's very confusing.
• ###### 3. Re: no gap in rownumber
Yes thanks, you're right, I'll try to explain that I want

IF I have the rows having column kreg, a sample, like as
``````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

• ###### 4. Re: no gap in rownumber
This works with an id column to order by, which you can can probably remove:
``````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``````
• ###### 5. Re: no gap in rownumber
You could probably use mod(kreg) and row_number() or another analytical function, but we'd need something to ORDER BY.
Can you post the FULL PICTURE and a WORKING TESTCASE (CREATE TABLE+INSERT INTO statements).
(Re)read {message:id=9360002} , especially #7, 8 and 9.
• ###### 6. Re: no gap in rownumber
Rosario Vigilante wrote:
I hope do is better now
Not really...

A good habit is to give either createTable+insert, or a with_clause so that we can give a try on your real problem, and not on something that we imagine is your problem...
Once given this sample input data, you show what output you're trying to get, and give explanation of the logic that leads to it.

On the imaginary part, maybe something in that flavor ?
``````[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``````
• ###### 7. Re: no gap in rownumber
Imho you need an order over kreg, i took id for example
``````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 ``````
• ###### 8. Re: no gap in rownumber
Thank you to all for time and patience, apologize to everyone

I solved with CASE in where, the variable 'serviceambula' is given as parameter in stored procedure where my query is into, ifserviceambula = 'S' I select rows with O.K_REGIME = '05' and viceversa:
``````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 again

Edited by: Rosario Vigilante on Nov 23, 2012 10:52 AM
• ###### 9. Re: no gap in rownumber
try this... if helps..
``````with 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; ``````
• ###### 10. Re: no gap in rownumber
Hi Rosario,

Your post doesn't explain how you have solved as you mentioned columns (serviceambula) which you haven't posted before.
Next time please post table structure and sample data.

People will be happy to help you are able to post information in the proper way as mentioned in SQL and PL/SQL FAQ

Regards.
Al
• ###### 11. Re: no gap in rownumber
``````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,
...
...``````
I'm still confused... what is K_REGIME ??

I got this.. please check ..
• ###### 12. Re: no gap in rownumber
Grazie

I hope do I done what you wrote me