This discussion is archived
8 Replies Latest reply: Nov 2, 2012 12:36 PM by Etbin RSS

Grouping Items by a Sequence

GhanaApexDeveloper Explorer
Currently Being Moderated
Hi All,
i have about 10,000 records and want to group them same name then and insert the same same sequence for the like in the group.
Eg.


1000000018     JOHN KARSSEY
1000000018     JOHN KARSSEY
1000000070     JOHN NIMO
1000000070     JOHN NIMO
1000000070     JOHN NIMO
1000000035     JOSEPH MENSAH
1000000035     JOSEPH MENSAH
1000000058     JOYCE TETTEH
1000000058     JOYCE TETTEH
1000000326     JUDITH SULIAH
1000000326     JUDITH SULIAH
1000000327     KADUA AZUGA
1000000327     KADUA AZUGA
1000000247     KARIM SABRATU
1000000247     KARIM SABRATU
1000000247     KARIM SABRATU
1000000247     KARIM SABRATU
1000000282     KARIM WINNAB
1000000282     KARIM WINNAB
1000000192     KAYABA AZARA

i hope my question satisfies a solution
Thanks.
  • 1. Re: Grouping Items by a Sequence
    Warren Tolentino Expert
    Currently Being Moderated
    maybe this?
    select distinct [column], ...
      from [table]
  • 2. Re: Grouping Items by a Sequence
    Etbin Guru
    Currently Being Moderated
    How do you want your sequence to look like in the situation as below
    1000000018     JOHN KARSSEY
    1000000018     JOHN KARSSEY
    1000000070     JOHN NIMO
    1000000070     JOHN NIMO
    1000000070     JOHN NIMO
    1000000077     JOHN NIMO  /* different person, same name */
    1000000035     JOSEPH MENSAH
    1000000035     JOSEPH MENSAH
    1000000058     JOYCE TETTEH
    1000000058     JOYCE TETTEH
    1000000326     JUDITH SULIAH
    1000000326     JUDITH SULIAH
    1000000327     KADUA AZUGA
    1000000327     KADUA AZUGA
    1000000247     KARIM SABRATU
    1000000247     KARIM SABRATU
    1000000247     KARIM SABRATU
    1000000247     KARIM SABRATU
    1000000282     KARIM WINNAB
    1000000282     KARIM WINNAB
    1000000192     KAYABA AZARA
    Regards

    Etbin
  • 3. Re: Grouping Items by a Sequence
    GhanaApexDeveloper Explorer
    Currently Being Moderated
    Yes i do, Sir.
  • 4. Re: Grouping Items by a Sequence
    Etbin Guru
    Currently Being Moderated
    Maybe
    select code,
           name,
           dense_rank() over (order by name) seq_1,
           dense_rank() over (order by name,code) seq_2
      from the_table
    Regards

    Etbin
  • 5. Re: Grouping Items by a Sequence
    ranit B Expert
    Currently Being Moderated
    Etbin wrote:
    How do you want your sequence to look like in the situation as below
    1000000018     JOHN KARSSEY
    1000000018     JOHN KARSSEY
    1000000070     JOHN NIMO
    1000000070     JOHN NIMO
    1000000070     JOHN NIMO
    1000000077     JOHN NIMO  /* different person, same name */
    1000000035     JOSEPH MENSAH
    1000000035     JOSEPH MENSAH
    1000000058     JOYCE TETTEH
    1000000058     JOYCE TETTEH
    1000000326     JUDITH SULIAH
    1000000326     JUDITH SULIAH
    1000000327     KADUA AZUGA
    1000000327     KADUA AZUGA
    1000000247     KARIM SABRATU
    1000000247     KARIM SABRATU
    1000000247     KARIM SABRATU
    1000000247     KARIM SABRATU
    1000000282     KARIM WINNAB
    1000000282     KARIM WINNAB
    1000000192     KAYABA AZARA
    Regards

    Etbin
    I'm not sure, but seems like the COLUMN1 value is the ID for a particular user(name) COLUMN2.
    So, i feel like this scenario is not valid.

    Ranit B.
  • 6. Re: Grouping Items by a Sequence
    905172 Newbie
    Currently Being Moderated
    create table tab1 (name varchar2(20));

    insert into tab1 values('aaa');

    insert into tab1 values('aaa');

    insert into tab1 values('bbb');

    insert into tab1 values('ccc');

    insert into tab1 values('ccc');

    insert into tab1 values('ddd');

    commit;

    create table tab2 (id number, name varchar2(20));

    insert into tab2 select dense_rank() over(order by name) id, name from tab1 order by name;

    commit;

    select * from tab2;

    ID     NAME

    1     aaa
    1     aaa
    2     bbb
    3     ccc
    3     ccc
    4     ddd

    i think it helps you
  • 7. Re: Grouping Items by a Sequence
    GhanaApexDeveloper Explorer
    Currently Being Moderated
    Still didn't get the result as i wanted, cos the sort grouped by the names had some omissions or some with the same name.
  • 8. Re: Grouping Items by a Sequence
    Etbin Guru
    Currently Being Moderated
    Another guess as you're not providing answers
    select code,
           name,
           dense_rank() over (order by name,code) seq
      from (select distinct
                   code,name
              from the_table
           )
     order by name,code
    or
    select code,
           name,
           dense_rank() over (order by name,code) seq
      from (select code,name
              from the_table
             group by code,name
           )
     order by name,code
    Regards

    Etbin

Legend

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