Re: Reduce To Ranges aggregate function

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 19 Oct 2006 19:53:22 +0200
Message-ID: <4537bb92$0$21889$426a74cc@news.free.fr>

"Mike King" <emailMK_at_excite.com> a écrit dans le message de news: 12jfcsjsafvmtc1_at_corp.supernews.com...
| Has someone ready created an aggregate function that reduces a set of
| numbers to a reduced form. I'm writing some middleware software that needs
| to know the IDs of some tuples but the query could return tens or thousands
| of tuples. I'm thinking if there were an aggregate function that would
| reduce the network traffic.
|
| select reduced_to_ranges(id)
| from some_large_table
| where some_field = some_value
|
| ID
| ----
| 1
| 2
| 3
| 4
| 5
| 11
| 12
| 45
|
| Reduced form: 1-5,11-12,45
|
|

SQL> select id from t order by id;

        ID

---

         1
         2
         3
         4
         5
        11
        12
        45

8 rows selected.

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 Received on Thu Oct 19 2006 - 12:53:22 CDT