Reputation: 13
Below is my table data structure.
select 100 id, 1 srno,0 amt from dual
union all
select 100 id, 2 srno, 1000 amt from dual
union all
select 100 id, 3 srno, 1000 amt from dual
union all
select 100 id, 4 srno, 0 amt from dual
union all
select 100 id, 5 srno, 2000 amt from dual
I want result like this,
ID From_Srno To_Srno amt
100 1 1 0
100 2 3 1000
100 4 4 0
100 5 5 2000
Thanks, Fame
Upvotes: 1
Views: 45
Reputation: 222722
This reads as a gaps-and-island problem, where you want to group together "adjacent" rows that have the same amt
.
I would recommend the difference between row numbers to define the groups:
select id, min(srno) from_srno, max(srno) max_srno, amt
from (
select t.*,
row_number() over(partition by id order by srno) rn1,
row_number() over(partition by id, amt order by srno) rn2
from mytable t
) t
group by id, amt, rn1 - rn2
ID | FROM_SRNO | MAX_SRNO | AMT --: | --------: | -------: | ---: 100 | 1 | 1 | 0 100 | 2 | 3 | 1000 100 | 4 | 4 | 0 100 | 5 | 5 | 2000
Upvotes: 1