Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2013
    Posts
    24

    Question Unanswered: number the repeating group of rows

    Hi

    I am working on oracle 10g.
    I need to number the repeating group of rows.
    e.g. My table is

    Value

    1
    2
    3
    1
    2
    3
    1
    2
    3

    I want to display it like giwvn nelow.
    value times_repeated
    1 1
    2 1
    3 1
    1 2
    2 2
    3 2
    1 3
    2 3
    3 3
    Can someone help me doing this?

    --P J S

  2. #2
    Join Date
    Jul 2013
    Posts
    24
    Hi,

    I did it using dense_rank.
    Thanks..
    --P J S

  3. #3
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by P J S View Post
    Hi,

    I did it using dense_rank.
    Thanks..
    --P J S
    Can you show us the code, because I cant see any code that could produce what you want from the example data that you supplied.

  4. #4
    Join Date
    Jul 2013
    Posts
    24
    Hi Pablolee,

    Sorry for replying late..
    did not get a chance to visit forum.
    Below is the way to get desired output.

    SQL> create table mytable
    2 (col number)
    3 /

    Table created.

    SQL> insert into mytable values(1);

    1 row created.

    SQL> insert into mytable values(2);

    1 row created.

    SQL> insert into mytable values(3);

    1 row created.

    SQL> insert into mytable values(1);

    1 row created.

    SQL> insert into mytable values(2);

    1 row created.

    SQL> insert into mytable values(3);

    1 row created.

    SQL> insert into mytable values(1);

    1 row created.

    SQL> insert into mytable values(2);

    1 row created.

    SQL> insert into mytable values(3);

    1 row created.

    SQL> select * from mytable
    2 /

    COL
    ----------
    1
    2
    3
    1
    2
    3
    1
    2
    3

    9 rows selected.

    Query used:
    select col,dense_rank() over (partition by col order by rowid) "rank"
    from mytable order by rowid;

    COL rank
    --------- ----------
    1 1
    2 1
    3 1
    1 2
    2 2
    3 2
    1 3
    2 3
    3 3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •