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

"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

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

"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

Top-posting is one way to shut me up...

"Frank van Bortel" <frank.van.bor...@gmail.com> a écrit dans le message de news: eh8hc5$cc...@news3.zwoll1.ov.home.nl...
| Michel Cadot schreef:
| >
| > One step further ;-)
|
| You get a kick out of this, don't you?

Sure, I do.

| I'm flabbergasted. (Nice word, huh?)

Nice and hopefully I have my Harrap's at hand!

| --
| Regards,
| Frank van Bortel
|
| Top-posting is one way to shut me up...

Regards
Michel Cadot

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:

        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.

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

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

select
   min(id), max(id)
from
  (
   select
     id,
     id - row_number() over (order by id) grp
   from t
  ) dt
group by grp

If id is not unique use dense_rank instead of row_number...

Dieter

"Dieter Noeth" <dno...@gmx.de> a écrit dans le message de news: ehdhat$sc...@online.de...

Neat solution!

Regards
Michel Cadot