"Mike King" <emai...@excite.com> a écrit dans le message de news: 12jfg0la73vv...@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
