4 Replies Latest reply: Feb 21, 2013 9:04 AM by HeSi9466 RSS

    Counting the rows having the same 3 fields but one different field

    HeSi9466
      Hi,

      I have a table with 5 rows. customerid,bankid,sixid,favcode,idx
      In this table there are many rows which are having the same customerid,bankid and favcode BUT different sixcode

      111,222,333,555,0
      111,222,444,555,0
      111,222,666,555,0
      111,222,777,555,0
      111,222,888,555,0
      -------
      111,333,222,555,0
      111,333,444,555,0
      111,333,777,555,0
      111,333,888,555,0
      111,333,999,555,0
      111,333,666,555,0

      I want to update the idx in a way to count these records and reset in every new round.
      for instance it would be like this after the update.

      111,222,333,555,0
      111,222,444,555,1
      111,222,666,555,2
      111,222,777,555,3
      111,222,888,555,4
      111,333,222,555,0
      111,333,444,555,1
      111,333,777,555,2
      111,333,888,555,3
      111,333,999,555,4
      111,333,666,555,5

      Do you have any idea about the SQL or PL/SQL which can do this?

      Note that the same records are not necessarily after each other in the database so there must be a full table scan to find out the records which have 3 same fields.

      If there is any ambiguity in my question please let me know and i'll explain more.


      Many thanks for your help.
        • 1. Re: Counting the rows having the same 3 fields but one different field
          Frank Kulash
          Hi,
          Hesam wrote:
          Hi,

          I have a table with 5 rows. customerid,bankid,sixid,favcode,idx
          In this table there are many rows which are having the same customerid,bankid and favcode BUT different sixcode
          When you say sixcode, do you mean sixid?
          >
          111,222,333,555,0
          111,222,444,555,0
          111,222,666,555,0
          111,222,777,555,0
          111,222,888,555,0
          -------
          111,333,222,555,0
          111,333,444,555,0
          111,333,777,555,0
          111,333,888,555,0
          111,333,999,555,0
          111,333,666,555,0

          I want to update the idx in a way to count these records and reset in every new round.
          for instance it would be like this after the update.

          111,222,333,555,0
          111,222,444,555,1
          111,222,666,555,2
          111,222,777,555,3
          111,222,888,555,4
          111,333,222,555,0
          111,333,444,555,1
          111,333,777,555,2
          111,333,888,555,3
          111,333,999,555,4
          111,333,666,555,5

          Do you have any idea about the SQL or PL/SQL which can do this?
          Use the analytic ROW_NUMBER function:
          MERGE INTO     table_x          dst
          USING   (
                   SELECT  customerid, bankid, sixid, favcode
                   ,         ROW_NUMBER () OVER ( PARTITION BY  customerid
                                                          ,             bankid
                                    ,             favcode
                                    ORDER BY      sixid
                                     ) - 1    AS idx
                   FROM    table_x
               )               src
          ON     (     dst.custiomerid     = src.customerid
               AND     dst,bankid     = src.bankid
               AND     dst.sixid     = src.sixid
               AND     dst.favcode     = src.favcode
               )
          WHEN MATCHED THEN UPDATE
          SET     dst.idx       = src.idx
          ;
          If you'd care to post CREATE TABLE and INSERT statements for the sample data, then I could test it.
          See the forum FAQ {message:id=9360002}
          Note that the same records are not necessarily after each other in the database
          Rows are never in any particular order.
          so there must be a full table scan to find out the records which have 3 same fields.

          If there is any ambiguity in my question please let me know and i'll explain more.


          Many thanks for your help.
          • 2. Re: Counting the rows having the same 3 fields but one different field
            HeSi9466
            For your first question i meant both same customerid and bankid and favcode but different sixcode.

            This is my Table:

            CREATE TABLE TEST.FAVE_CODE2_NEW_JOIN
            (
            BANKID VARCHAR2(5 BYTE) NOT NULL,
            CUSTOMERID VARCHAR2(12 BYTE) NOT NULL,
            SIXID VARCHAR2(12 BYTE) NOT NULL,
            FAVCODE VARCHAR2(22 BYTE),
            IDX NUMBER(4)
            )
            TABLESPACE TEST
            PCTUSED 0
            PCTFREE 10
            INITRANS 1
            MAXTRANS 255
            STORAGE (
            INITIAL 64K
            NEXT 1M
            MINEXTENTS 1
            MAXEXTENTS UNLIMITED
            PCTINCREASE 0
            BUFFER_POOL DEFAULT
            )
            LOGGING
            NOCOMPRESS
            NOCACHE
            NOPARALLEL
            MONITORING;



            and i want to update to idx based on the logic i mentioned on my previous post.

            Thanks again.

            Edited by: Hesam on Feb 21, 2013 3:59 PM
            • 3. Re: Counting the rows having the same 3 fields but one different field
              Frank Kulash
              Hi,
              Hesam wrote:
              For your first question i meant both same customerid and bankid and favcode but different sixcode.

              This is my Table:

              CREATE TABLE TEST.FAVE_CODE2_NEW_JOIN ...
              Thanks. Don't forget to post INSERT statements for some sample data, and the results you want from that data (if the results are any different from what you posted earlier).

              What's wrong with the MERGE statement I posted? Point out where it is getting the wrong results (given the sample data you provide), and explain how you get the right results in those places.
              • 4. Re: Counting the rows having the same 3 fields but one different field
                HeSi9466
                I think there was no problem with the MERGE you were sending me.

                It seems that all the records are absolutelly correct now. Unless you say it might have problems?? :D:D:D

                Actually there are lots of records and i cant really check all of them BUT i cant see any problem and they are all indexed now.

                Many Thanks for the help.