Discussions
Categories
 382.3K All Categories
 2.1K Data
 209 Big Data Appliance
 1.9K Data Science
 448K Databases
 221K General Database Discussions
 3.7K Java and JavaScript in the Database
 25 Multilingual Engine
 520 MySQL Community Space
 467 NoSQL Database
 7.8K Oracle Database Express Edition (XE)
 2.9K ORDS, SODA & JSON in the Database
 492 SQLcl
 3.9K SQL Developer Data Modeler
 186.2K SQL & PL/SQL
 21K SQL Developer
 293.3K Development
 7 Developer Projects
 128 Programming Languages
 290K Development Tools
 95 DevOps
 3K QA/Testing
 645.5K Java
 24 Java Learning Subscription
 36.9K Database Connectivity
 149 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.9K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 16 Java Essentials
 144 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 198 Java User Groups
 24 JavaScript  Nashorn
 Programs
 266 LiveLabs
 36 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.8K Other Languages
 2.3K Chinese
 166 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 230 Portuguese
Group by preserving the order
jeneesh
Member Posts: 7,168
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

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? 
Thanks for the reply
col1 col2 col3
1 5 1
1 10 2summed here, coz col1 changes
2 2 3
2 5 4summed here, coz col1 changes
1 15 5summed here, coz col1 changes
3 25 6summed here
Message was edited by:
jeneesh 
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. 
Thanks!!!
Nicely done and saved my life.. 
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 smlag(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

Thanks for ur patience, Mohana..

Elic's solution is very great.
another SQLSQL> 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 
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
In above solution,
 
1 15
2 7
1 15
3 55
4 5
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 
Elic wrote:I've learned a lot, Your answer is very helpful.
<DIV class=jivequote>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,sansserif" 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>
This discussion has been closed.