I need to query a table, containing a step id + value. The result will list the intervals, along with their associated value. Intervals here are defined as "succession of contiguous ids of steps, sharing same data value".
I'm having a hard time describing it in words, so please see this:
From this table
Step ! Data
------------
1 ! A
2 ! A
3 ! A
5 ! A
6 ! B
10 ! A
I need the following report
From ! To ! Data
-------------------
1 ! 3 ! A
5 ! 5 ! A
6 ! null ! B
10 ! null ! A
I thought lead() would help me out here, but did not succeed.
select min (step) as "from"
,nullif (max (step),max(min(step)) over (partition by data)) as "to"
,data
from (select step,data
,row_number () over (partition by data order by step) as n
from t
)
group by data
,step - n
order by "from"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With