4 Replies Latest reply on Jun 17, 2019 8:50 AM by BrunoVroman

    GROUP

    Jelly

          WITH T as(

          select 'A' NAME,1 SEQ from DUAL

          UNION ALL

          select 'B' NAME,2 SEQ from DUAL

          UNION ALL

          select 'C' NAME,2 SEQ from DUAL

          UNION ALL

          select 'D' NAME,2 SEQ from DUAL  

          UNION ALL

          select 'E' NAME,1 SEQ from DUAL     

          UNION ALL

          select 'F' NAME,2 SEQ from DUAL         

          UNION ALL

          select 'G' NAME,2 SEQ from DUAL)

          SELECT * FROM T

       

      I WISH  SELECT RESULT

      WHEN SEQ=1,GROUP+1

       

      NAME GROUP

      A            1

      B            1

      C            1

      D            1

      E            2

      F            2

      G            2

        • 1. Re: GROUP
          Cookiemonster76

          Is the data supposed to be ordered by name?

          Or do you need another column to order it?

          • 2. Re: GROUP
            John_K
            with
              t as
                (select 'A' name, 1 seq from dual
                 union all
                 select 'B' name, 2 seq from dual
                 union all
                 select 'C' name, 2 seq from dual
                 union all
                 select 'D' name, 2 seq from dual
                 union all
                 select 'E' name, 1 seq from dual
                 union all
                 select 'F' name, 2 seq from dual
                 union all
                 select 'G' name, 2 seq from dual)
            select name, seq, sum(decode(seq,1,1)) over (order by name) grp
              from t
            order by name
            

             

             

            NAME        SEQ        GRP
            ---- ---------- ----------
            A             1          1
            B             2          1
            C             2          1
            D             2          1
            E             1          2
            F             2          2
            G             2          2
            

             

             

            Assuming your order is by name.

            • 3. Re: GROUP
              cormaco
              SELECT
                  name,sum(case seq when 1 then 1 else 0 end) over (order by name) as grp
              FROM
                  t
              
              
              • 4. Re: GROUP
                BrunoVroman

                Hello,

                 

                note that you have to explicit an ORDER in your data otherwise the results might "surprise" you...
                I will assume that we order by "name".

                 

                And note that "group" is a reserved word, so you should not use it to name a column (if you insist you can use "COLUMN HEADER" in sqllus and or use double quotes but this is rather a bad idea)

                 

                WITH t AS(
                SELECT 'A' name, 1 seq FROM dual
                UNION ALL SELECT 'B', 2 FROM dual
                UNION ALL SELECT 'C', 2 FROM dual
                UNION ALL SELECT 'D', 2 FROM dual
                UNION ALL SELECT 'E', 1 FROM dual
                UNION ALL SELECT 'F', 2 FROM dual
                UNION ALL SELECT 'G', 2 FROM dual
                UNION ALL SELECT 'H', 1 FROM dual
                UNION ALL SELECT 'I', 1 FROM dual
                UNION ALL SELECT 'J', 2 FROM dual
                UNION ALL SELECT 'K', 3 FROM dual
                UNION ALL SELECT 'L', 4 FROM dual
                UNION ALL SELECT 'M', 1 FROM dual
                )
                SELECT name
                     , SUM( CASE WHEN seq = 1 THEN 1 ELSE 0 END ) OVER ( ORDER BY name RANGE UNBOUNDED PRECEDING ) grp
                  FROM t
                  ORDER BY name
                ;
                N  GRP
                - ----
                A    1
                B    1
                C    1
                D    1
                E    2
                F    2
                G    2
                H    3
                I    4
                J    4
                K    4
                L    4
                M    5
                13 rows selected.

                 

                Best regards,

                 

                Bruno Vroman.