Re: Reduce To Ranges aggregate function

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 19 Oct 2006 20:29:09 +0200
Message-ID: <4537c3f6$0$3263$426a74cc@news.free.fr>

"Mike King" <emailMK_at_excite.com> a écrit dans le message de news: 12jfg0la73vvk9e_at_corp.supernews.com...
|> SQL> col Range format a20
| > SQL> with
| > 2 step1 as (
| > 3 select id,
| > 4 case
| > 5 when nvl(lag(id) over (order by id),-1) != id-1 then id
| > 6 end grp
| > 7 from t),
| > 8 step2 as (
| > 9 select id,
| > 10 max(grp) over (order by id) grp
| > 11 from step1
| > 12 )
| > 13 select min(id)||decode(min(id),max(id),'','-'||max(id)) "Range"
| > 14 from step2
| > 15 group by grp
| > 16 /
| > Range
| > --------------------
| > 1-5
| > 11-12
| > 45
| >
| > 3 rows selected.
| >
| > Regards
| > Michel Cadot
|
|
| I agree with Frank. Wow! Thanks!
|
|

One step further ;-)

SQL> with
  2 step1 as (

  3      select id,
  4             case
  5             when nvl(lag(id) over (order by id),-1) != id-1 then id
  6             end grp
  7      from t),
  8    step2 as (
  9      select id,
 10             max(grp) over (order by id) grp
 11      from step1

 12 ),
 13 step3 as (

 14      select min(id)||decode(min(id),max(id),'','-'||max(id)) rg,
 15             row_number () over (order by min(id)) rn
 16      from step2
 17      group by grp

 18 )
 19 select max(substr(sys_connect_by_path(rg,','),2)) "Reduced"  20 from step3
 21 connect by prior rn = rn-1
 22 start with rn-1 = 0
 23 /
Reduced

---

1-5,11-12,45

Regards
Michel Cadot Received on Thu Oct 19 2006 - 13:29:09 CDT