Charles Hooper wrote:
> Michel Cadot wrote:
> > "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

> I guess that I have a bit of difficulty understanding what the OP was
> wanting.  That is an interesting solution that you posted Michel.  I am
> not sure that I would have tried to use analytical functions to solve
> the problem, but your example is clever.

> If we can make the assumption that the OP was looking for a way to
> group the numbers 1-10, 11-20, 21-30, 31-40, 41-50, a much simplier
> approach is as follows:

> CREATE TABLE T (ID NUMBER(22));
> INSERT INTO T VALUES (1);
> INSERT INTO T VALUES (2);
> INSERT INTO T VALUES (3);
> INSERT INTO T VALUES (4);
> INSERT INTO T VALUES (5);
> INSERT INTO T VALUES (11);
> INSERT INTO T VALUES (12);
> INSERT INTO T VALUES (45);

> SELECT
>   MIN(ID)||DECODE(MAX(ID),MIN(ID),'','-'||MAX(ID)) RANGE
> FROM
>   T
> GROUP BY
>   TRUNC((ID-1)/10)
> ORDER BY
>   TRUNC((ID-1)/10);

> RANGE
> ======
> 1-5
> 11-12
> 45

> SELECT

> MAX(DECODE(ROWNUM,1,RANGE,''))||MAX(DECODE(ROWNUM,2,','||RANGE,''))||MAX(DE CODE(ROWNUM,3,','||RANGE,''))||MAX(DECODE(ROWNUM,4,','||RANGE,''))||MAX(DEC ODE(ROWNUM,5,','||RANGE,''))||MAX(DECODE(ROWNUM,6,','||RANGE,''))
> REDUCED_FORM
> FROM
>   (SELECT
>     MIN(ID)||DECODE(MAX(ID),MIN(ID),'','-'||MAX(ID)) RANGE
>   FROM
>     T
>   GROUP BY
>     TRUNC((ID-1)/10)
>   ORDER BY
>     TRUNC((ID-1)/10)) T;

> REDUCED_FORM
> ============
> 1-5,11-12,45

> Looking at the OP's suggested SQL statement:
> > | select reduced_to_ranges(id)
> > | from some_large_table
> > | where some_field = some_value

> SELECT
>   ID,
>   TRUNC((ID-1)/10) RANGE
> FROM
>   T;

>    ID      RANGE
> ========== ==========
>     1          0
>     2          0
>     3          0
>     4          0
>     5          0
>    11          1
>    12          1
>    45          4

> INSERT INTO T VALUES (100);

> SELECT
>   TRUNC((ID-1)/10) RANGE
> FROM
>   T
> WHERE
>   ID=100;

> RANGE
> ==========
>  9

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.