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