On Wed, 1 Mar 2006 00:28:54 +0100, "Michel Cadot"
<micadot{at}altern{dot}org> wrote:
...
>SQL> with
> 2 step1 as (
> 3 select d_no, val,
> 4 lag (d_no) over (order by d_no, val) prev_no,
> 5 lag (val) over (order by d_no, val) prev_val,
> 6 row_number() over (order by d_no, val) rn
> 7 from t1
> 8 ),
> 9 step2 as (
> 10 select d_no, val,
> 11 case when prev_no is null or prev_no != d_no
> 12 or prev_val != val-1
> 13 then rn
> 14 end rn
> 15 from step1
> 16 ),
> 17 step3 as (
> 18 select d_no, val,
> 19 max(rn) over (order by d_no, val) group_nb
> 20 from step2
> 21 )
> 22 select d_no,
> 23 min(val)||decode(max(val),min(val),'','-'||max(val))
> 24 the_range
> 25 from step3
> 26 group by d_no, group_nb
> 27 order by d_no
> 28 /
> D_NO THE_RANGE
>---------- -----------------------------------------------------------
> 1 20-23
> 1 25
> 1 1503-1504
> 3 502-504
> 3 600
>
>5 rows selected.
>
>Regards
>Michel Cadot
>
>
Elegant!
Jaap.
Received on Wed Mar 01 2006 - 05:01:06 CST
