8 Replies Latest reply: Nov 2, 2012 2:36 PM by Etbin RSS

    Grouping Items by a Sequence

    GhanaApexDeveloper
      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
          maybe this?
          select distinct [column], ...
            from [table]
          • 2. Re: Grouping Items by a Sequence
            Etbin
            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
              Yes i do, Sir.
              • 4. Re: Grouping Items by a Sequence
                Etbin
                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
                  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
                    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
                      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
                        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