Group by preserving the order

jeneesh · Nov 29, 2006 1:41AM

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

474126 · Nov 29, 2006 1:51AM

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?

jeneesh · Nov 29, 2006 1:56AM

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

121256 · Nov 29, 2006 2:27AM

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.

jeneesh · Nov 29, 2006 2:32AM

Thanks!!!
Nicely done and saved my life..

474126 · Nov 29, 2006 2:38AM

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

jeneesh · Nov 29, 2006 2:43AM

Thanks for ur patience, Mohana..

Aketi Jyuuzou · Jan 15, 2007 2:57AM

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

Aketi Jyuuzou · Oct 9, 2007 8:53AM

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);

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
467872
569024
450745
569179

828920 · Jan 5, 2011 8:52PM

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.

Forum Stats

Quick Links

Categories

Group by preserving the order

Comments

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Group by preserving the order

Comments