This discussion is archived
12 Replies Latest reply: Nov 23, 2012 1:56 AM by Rosario Vigilante RSS

no gap in rownumber

Rosario Vigilante Journeyer
Currently Being Moderated
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,
...
...
thanks in advance
  • 1. Re: no gap in rownumber
    Nicosa Expert
    Currently Being Moderated
    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
    ranit B Expert
    Currently Being Moderated
    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,
    ...
    ...
    thanks in advance
    Please explain it clearly. It's very confusing.
    Also, post sample data and help us to help you.
  • 3. Re: no gap in rownumber
    Rosario Vigilante Journeyer
    Currently Being Moderated
    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

    Thanks for your time
  • 4. Re: no gap in rownumber
    bencol Pro
    Currently Being Moderated
    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
    Hoek Guru
    Currently Being Moderated
    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
    Nicosa Expert
    Currently Being Moderated
    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
    chris227 Guru
    Currently Being Moderated
    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
    Rosario Vigilante Journeyer
    Currently Being Moderated
    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
    ranit B Expert
    Currently Being Moderated
    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
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Rosario,

    if you have solved your problem please mark this question as answered.

    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
    ranit B Expert
    Currently Being Moderated
    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
    Rosario Vigilante Journeyer
    Currently Being Moderated
    Grazie

    I hope do I done what you wrote me

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points