## Forum Stats

• 3,768,190 Users
• 2,252,758 Discussions
• 7,874,486 Comments

Discussions

# Group by preserving the order

Member Posts: 7,168
edited Jan 5, 2011 8:52PM
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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ```

## Comments

• Member Posts: 914
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?
• Member Posts: 7,168
edited Nov 29, 2006 1:58AM
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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        ```
• Member Posts: 1,054
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.```
• Member Posts: 7,168
Thanks!!!
Nicely done and saved my life..
• Member Posts: 914
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```
• Member Posts: 7,168
Thanks for ur patience, Mohana..
• Member Posts: 1,072 Bronze Badge
edited Feb 26, 2007 12:30AM
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```
• Member Posts: 1,072 Bronze Badge
edited Oct 10, 2007 9:13AM
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
467872
569024
450745
569179
• Member Posts: 1
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.
This discussion has been closed.