1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL to generate sequential numbers for each repeating row and reset upon new key

Discussion in 'SQL PL/SQL' started by yasar2002, Dec 12, 2013.

  1. yasar2002

    yasar2002 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Dubai
    Hi all,

    I know how to increment a number in a column using PL/SQL for every row that repeats. In the example below, ColB contains such data which needs to be displayed as autoincrement as 1 but to reset once value in colA changes.

    I have data in a table that has repititive rows such as:
    ColA ColB ColC
    Row1: 8 1 x
    Row2: 8 1 x
    Row3: 8 1 x
    Row4: 9 1 x
    Row5: 9 1 x

    The desired format is:
    ColA ColB ColC
    Row1: 8 1 x
    Row2: 8 2 x
    Row3: 8 1 x
    Row4: 9 2 x
    Row5: 9 3 x

    Please advise if it is doable in SQL directly.

    Thanks,

    YM
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    Read about analytic functions of oracle :

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#SQLRF51207

    http://www.oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions.php

    here simple example, if task description correspond to an example of data:

    Code (SQL):


    WITH
    example_of_data AS
    (
     SELECT 8 AS cola ,  'x' AS colc  FROM dual UNION ALL
     SELECT 8 AS cola ,  'x' AS colc  FROM dual UNION ALL
     SELECT 8 AS cola ,  'x' AS colc  FROM dual UNION ALL
     SELECT 9 AS cola ,  'x' AS colc  FROM dual UNION ALL
     SELECT 9 AS cola ,  'x' AS colc  FROM dual
    )
    SELECT
         ed.cola,
         ROW_NUMBER () OVER (partition BY ed.cola ORDER BY rownum ) colb,
         ed.colc
    FROM example_of_data ed;


    SQL>
          COLA       COLB COLC
    ---------- ---------- ----
             8          1 x
             8          2 x
             8          3 x
             9          1 x
             9          2 x


     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Is this strictly generated by a query or do you intend to store such 'sequencing' in a table?
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    These data always can be obtained on the fly and they don't need to be stored
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That isn't my question. I have seen users want to store such 'sequencing' in the database table and use it for referential purposes but since such 'sequencing' is transactional such implementations can fail miserably when simultaneous transactions occur.
     
  6. yasar2002

    yasar2002 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Dubai
    Thanks all for your response. I intend to generate a number on the fly and reset it based on another column (used as the identifier of 1 or more rows). So, when a new set of record comes grouped by 1 column, numbering restarts and autoincrements till the last record in the group. and so on. I hope Ihave explained what I want.
     
  7. yasar2002

    yasar2002 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Dubai
    Solution : row_number() over (partition by jpnum order by jpnum)
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It does help tremendously to read the entire thread to prevent posting the same information again. This solution was first posted on December 8, 2012.