9 Replies Latest reply: Jan 5, 2011 7:52 PM by 828920 RSS

    Group by preserving the order

    jeneesh
      Say for eg: my data is like this
      --order by col3
      col1 col2  col3
      1       5     1
      1      10    2
      2      2      3
      2      5      4
      1     15     5
      I hav to get o/p as
      col1  sum_col2
      1       15
      2       7
      1      15
      I have to group by preserving the order
      Pls give me suggestions..

      jeneesh                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
        • 1. Re: Group by preserving the order
          474126
          What do you mean preserving the order? In your example, how are you grouping the col2?. If you sum the col2 based on col1, you would get

          col1 sum_col2
          1 30
          2 7

          I don't understand how the col2 is summed up? Can you explain?
          • 2. Re: Group by preserving the order
            jeneesh
            Thanks for the reply
            col1 col2  col3
            1       5     1
            1      10    2--summed here, coz col1 changes
            2      2      3
            2      5      4--summed here, coz col1 changes
            1     15     5--summed here, coz col1 changes
            3      25   6--summed here

            Message was edited by:
                    jeneesh                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
            • 3. Re: Group by preserving the order
              121256
              I have to group by preserving the order
              with t as
              ( select 1 as col1,  5 as col2, 1 as col3 from dual union all
                select 1 as col1, 10 as col2, 2 as col3 from dual union all
                select 2 as col1,  2 as col2, 3 as col3 from dual union all
                select 2 as col1,  5 as col2, 4 as col3 from dual union all
                select 1 as col1, 15 as col2, 5 as col3 from dual
              )
              select col1, sum(col2) as col2
                from
                ( select col1, col2, sum(start_of_group) over (order by col3) as group_no
                    from
                    ( select col1, col2, col3,
                          decode(lag(col1) over (order by col3), col1, 0, 1) as start_of_group
                        from t
                    )
                )
                group by group_no, col1
              ;

                       COL1          COL2
              ------------- -------------
                          1            15
                          2             7
                          1            15

              3 rows selected.
              • 4. Re: Group by preserving the order
                jeneesh
                Thanks!!!
                Nicely done and saved my life..
                • 5. Re: Group by preserving the order
                  474126
                  Elic, Simple and neat solution. I was trying something similar and got this complicated solution.
                  SQL> with t
                    2  as
                    3  (
                    4  select 1 col1,5 col2,1 col3 from dual
                    5  union all
                    6  select 1,10,2 from dual
                    7  union all
                    8  select 2,2,3 from dual
                    9  union all
                   10  select 2,5,4 from dual
                   11  union all
                   12  select 1,15,5 from dual
                   13  union all
                   14  select 3,25,6 from dual
                   15  union all
                   16  select 3,10,7 from dual
                   17  union all
                   18  select 3,5,8 from dual
                   19  union all
                   20  select 3,15,9 from dual
                   21  union all
                   22  select 4,5,10 from dual
                   23  )
                   24  select col1,case when s=1 and rownum = 1 then sm
                   25                     when s=1 and rownum <> 1 then  sm-lag(sm,1) over(partition by 1 order by col3)
                   26                   when s is null then col2
                   27               end     col2
                   28  from
                   29  (
                   30  select col1,col2,col3,s,sm
                   31  from
                   32  (select col1,col2,col3,
                   33         case when col1=lag(col1,1) over(partition by 1 order by col3) and col1<>lead(col1,1) over(partition by 1 order by col3)
                   34                  then 1
                   35             when col1=lead(col1,1) over(partition by 1 order by col3)
                   36                  then 0
                   37             else null end s,
                   38         sum(col2) over(order by col3) sm
                   39  from t
                   40  )
                   41  where s=1
                   42  or s is null
                   43  order by col3
                   44  )
                   45  /
                  
                        COL1       COL2
                  ---------- ----------
                           1         15
                           2          7
                           1         15
                           3         55
                           4          5
                  • 6. Re: Group by preserving the order
                    jeneesh
                    Thanks for ur patience, Mohana..
                    • 7. Re: Group by preserving the order
                      Aketi Jyuuzou
                      Elic's solution is very great.


                      another SQL
                      SQL> with WorkView as(
                        2  select 1 ID,5 Val,1 SortOrder from dual
                        3  union all select 1,10,2 from dual
                        4  union all select 2,2,3 from dual
                        5  union all select 2,5,4 from dual
                        6  union all select 1,15,5 from dual
                        7  union all select 3,25,6 from dual
                        8  union all select 3,10,7 from dual
                        9  union all select 3,5,8 from dual
                      10  union all select 3,15,9 from dual
                      11  union all select 4,5,10 from dual)
                      12  select ID,sum(Val) as Val
                      13    from (select ID,Val,Last_Value(LagSortOrder ignore nulls) over(order by SortOrder) as LagSortOrder
                      14            from (select ID,Val,SortOrder,
                      15                         case when ID != Lag(ID) over(order by SortOrder)
                      16                              then SortOrder end as LagSortOrder
                      17                    from WorkView))
                      18  group by ID,LagSortOrder
                      19  order by LagSortOrder nulls first;

                      ID  VAL
                      --  ---
                      1   15
                      2    7
                      1   15
                      3   55
                      4    5
                      • 8. Re: Group by preserving the order
                        Aketi Jyuuzou
                        I have reserched solution which this problem can solve.
                        Therefore, I created this solution.

                        Below solution consults "Tabibitozan".
                        "Tabibitozan" is Japanese language.
                        "Tabibitozan" is one of math problem. (http://www.manabinoba.com/index.cfm/4,757,73,html?year=2002)
                        I do not know what "Tabibitozan" is called in English.
                        with ValTable as(
                        select 1 ID,5 Val,1 SortOrder from dual
                        union all select 1,10,2 from dual
                        union all select 2,2,3 from dual
                        union all select 2,5,4 from dual
                        union all select 1,15,5 from dual
                        union all select 3,25,6 from dual
                        union all select 3,10,7 from dual
                        union all select 3,5,8 from dual
                        union all select 3,15,9 from dual
                        union all select 4,5,10 from dual)
                        select ID,sum(Val) as Val
                        from (select ID,Val,SortOrder,
                               Row_Number() over(order by SortOrder)
                              -Row_Number() over(partition by ID order by SortOrder) as makeGroup
                              from ValTable)
                        group by ID,makeGroup
                        order by min(SortOrder);
                        ID  VAL
                        --  ---
                        1   15
                        2    7
                        1   15
                        3   55
                        4    5
                        In above solution,
                        I imagined that there are 5 people called X,A,B,C, and D.

                        X always walks 1m. (Row_Number() over(order by SortOrder))

                        When ID = 1, A walks 1m. (Row_Number() over(partition by ID order by SortOrder))
                        When ID = 2, B walks 1m. (Row_Number() over(partition by ID order by SortOrder))
                        When ID = 3, C walks 1m. (Row_Number() over(partition by ID order by SortOrder))
                        When ID = 4, D walks 1m. (Row_Number() over(partition by ID order by SortOrder))

                        Then,
                        I derived distance between X and A.
                        I derived distance between X and B.
                        I derived distance between X and C.
                        I derived distance between X and D.

                        Then, I made group using these distance. (group by ID,makeGroup)



                        similar threads
                        SQL - Hierarchical View
                        Version Key creation
                        Group by preserving the order
                        Is it possible to reset agg function row_number()
                        • 9. Re: Group by preserving the order
                          828920
                          Elic wrote:

                          <DIV class=jive-quote>I have to group by preserving the order</DIV><PRE>with t as

                          ( select 1 as col1, 5 as col2, 1 as col3 from dual union all

                          select 1 as col1, 10 as col2, 2 as col3 from dual union all

                          select 2 as col1, 2 as col2, 3 as col3 from dual union all

                          select 2 as col1, 5 as col2, 4 as col3 from dual union all

                          select 1 as col1, 15 as col2, 5 as col3 from dual

                          )

                          select col1, sum(col2) as col2

                          from

                          ( select col1, col2, sum(start_of_group) over (order by col3) as group_no

                          from

                          ( select col1, col2, col3,

                          <font face="tahoma,verdana,sans-serif" size="1" color="#000">decode</font>(lag(col1) over (order by col3), col1, 0, 1) as start_of_group

                          from t

                          )

                          )

                          group by group_no, col1

                          ;



                          COL1 COL2

                          <HR>-------------

                          1 15

                          2 7

                          1 15



                          3 rows selected.

                          </PRE>
                          I've learned a lot, Your answer is very helpful.