1 2 Previous Next 27 Replies Latest reply on Mar 14, 2008 10:28 AM by 627527

# How to group numbers into ranges?

Hi there,

I have table with a numerical ID field. The values in this column may or may not be consecutive. For example:

ID
--
1
2
4
5
6
8
9
10
11
12

Note, there are missing numbers. I need a query that will group the numbers into ranges like below:

LowerBound UpperBound
1 2
4 6
8 12

Any assistance appreciated! Thank you!
• ###### 1. Re: How to group numbers into ranges?
this..?
```SQL> select * from test;

ID
----------
1
2
3
6
8
9
11
12

8 rows selected.

SQL> select min(id) lb,max(id) ub
2  from (select id,id - row_number() over(order by id) rn
3        from test)
4  group by rn
5  order by 1;

LB         UB
---------- ----------
1          3
6          6
8          9
11         12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                ```
• ###### 2. Re: How to group numbers into ranges?
I'm sure someone will come along with a nice MODEL clause to do it, but just using basic connect by technology...
``````SQL> ed
Wrote file afiedt.buf

1  WITH t AS (select 1 as id from dual union all
2             select 2 from dual union all
3             select 4 from dual union all
4             select 5 from dual union all
5             select 6 from dual union all
6             select 8 from dual union all
7             select 9 from dual union all
8             select 10 from dual union all
9             select 11 from dual union all
10             select 12 from dual)
11  -- end of test data
12  , t2 as (select id, level lvl
13           from t
14           connect by id = prior id + 1)
15  , t3 as (select id, id+1 exp_next, lvl
16           from t2
17           where not exists (select id from t2 x where x.id = t2.id and x.lvl > t2.lvl)
18          )
19  select (select min(id) from t3 x connect by id = prior exp_next start with id = t3.id) min_id
20       , (select max(id) from t3 x connect by id = prior exp_next start with id = t3.id) max_id
21  from t3
22  where lvl = 1
23* order by id
SQL> /

MIN_ID     MAX_ID
---------- ----------
1          2
4          6
8         12

SQL>``````
• ###### 3. Re: How to group numbers into ranges?
> I'm sure someone will come along with a nice MODEL clause to do it

I wouldn't use the model clause here because it can be done as simple as jeneesh showed.

Regards,
Rob.
• ###### 4. Re: How to group numbers into ranges?
Erm, but Janeesh's solution didn't output the correct results. ?!?
• ###### 5. Re: How to group numbers into ranges?
> Erm, but Janeesh's solution didn't output the correct results. ?!?

Jeneesh confusingly used another data set, but his solution works:
```SQL> WITH t AS (select 1 as id from dual union all
2             select 2 from dual union all
3             select 4 from dual union all
4             select 5 from dual union all
5             select 6 from dual union all
6             select 8 from dual union all
7             select 9 from dual union all
8             select 10 from dual union all
9             select 11 from dual union all
10             select 12 from dual)
11  -- end of test data
12  select min(id) lb
13       , max(id) ub
14    from ( select id
15                , id - row_number() over(order by id) rn
16             from t
17         )
18   group by rn
19   order by 1
20  /

LB         UB
---------- ----------
1          2
4          6
8         12

3 rijen zijn geselecteerd.```
Regards,
Rob.
• ###### 6. Re: How to group numbers into ranges?
You may not need MODEL or hierachical query here :
```SQL> with mytbl as
2  (select 1  id from dual union all
3   select 2  id from dual union all
4   select 4  id from dual union all
5   select 5  id from dual union all
6   select 6  id from dual union all
7   select 8  id from dual union all
8   select 9  id from dual union all
9   select 10 id from dual union all
10   select 11 id from dual union all
11   select 12 id from dual )
12  select min(id), max(id)
13  from   (select id, id-row_number() over (order by id) rid from   mytbl)
14  group by rid;

MIN(ID)    MAX(ID)
---------- ----------
1          2
4          6
8         12

SQL> ```
The problem now may come if some values are duplicated into the source table.

Nicolas.

Rob beat me, and has good eyes on the Jeneesh's dataset
Message was edited by:
N. Gasparotto
• ###### 7. Re: How to group numbers into ranges?
The problem now may come if some values are duplicated into the source table
Maybe dense_rank() may workaround :
```SQL> with mytbl as
2  (select 1  id from dual union all
3   select 2  id from dual union all
4   select 4  id from dual union all
5   select 5  id from dual union all
6   select 6  id from dual union all
7   select 8  id from dual union all
8   select 9  id from dual union all
9   select 10 id from dual union all
10 select 11 id from dual union all
11 select 11 id from dual union all
12   select 12 id from dual )
13  select min(id), max(id)
14  from   (select id, id-dense_rank() over (order by id) rid from mytbl)
15  group by rid;

MIN(ID)    MAX(ID)
---------- ----------
1          2
4          6
8         12```
Nicolas.
• ###### 8. Re: How to group numbers into ranges?
> Erm, but Janeesh's solution didn't output the
correct results. ?!?

Jeneesh confusingly used another data set, but his
solution works:
Ah, my mistake. Yes it did confuse me.
• ###### 9. Re: How to group numbers into ranges?
I'm sure someone will come along with a nice MODEL
clause to do it, but just using basic connect by
technology...
I couldn't resist, although there are better alternatives already in this thread.
``````WITH t AS (select 1 as id from dual union all
select 2 from dual union all
select 4 from dual union all
select 5 from dual union all
select 6 from dual union all
select 8 from dual union all
select 9 from dual union all
select 10 from dual union all
select 11 from dual union all
select 12 from dual
)
SELECT DISTINCT
id_min
, id_max
FROM t
MODEL
DIMENSION BY (ROW_NUMBER() OVER (ORDER BY t.id) rn)
MEASURES (t.id, t.id id_min, t.id id_max)
RULES (id_min[rn] = CASE WHEN NVL(id[CV(rn)] - id[CV(rn)-1], 2) > 1  THEN id[CV()] ELSE id_min[CV()-1] END
,id_max[rn] = MAX(id) OVER (PARTITION BY id_min)
)
;    ``````
C.
• ###### 10. Re: How to group numbers into ranges?
Thanks so much guys! Really appreciate this! You make it look so easy!

I was wondering how efficient is this query? If the table had millions of rows, how fast/slow would this query run?
• ###### 11. Re: How to group numbers into ranges?
Hi,

An extension to this question (sort of). I was wondering if it was possible to determine the numbers that are not in the table? And if possible, group the numbers that are missing into ranges.

For example the table contains:

ID
---
1
2
6
7
10

The query will return:

LB UB
----- -----
3 5
8 9

If the grouping into ranges is not possible, then if it can return the list of missing numbers, then that would be ok too.

Thanks!
• ###### 12. Re: How to group numbers into ranges?
group the numbers that are missing into ranges.
``select * from (select id + 1 as lb, lead(id) over (order by id) - 1 as ub from t) where lb <= ub;``
• ###### 13. Re: How to group numbers into ranges?
Hi Friends

i have a similar kind of problem but in my case the numbers are in range ie, From and To Eg

select * from Used_Ranges;

ID MY_FROM MY_TO
----- ---------- ----------
1 4 7
2 9 10

i want to search the missing ranges between 1 and 10 ie i want the output as
from To
1 3
8 8

can anyone help me
• ###### 14. Re: How to group numbers into ranges?
in my case the numbers are in range
i want to search the missing ranges between 1 and 10
```with t as
( select 4 as my_from,  7 as my_to from dual union all
select 9 as my_from, 10 as my_to from dual
)
select *
from
( select lag(my_to + 1, 1, 1) over (order by my_from) as lb, my_from - 1 as ub from t
union all
select max(my_to) + 1, 10 from t
)
where lb <= ub
;

LB            UB
------------- -------------
1             3
8             8```
1 2 Previous Next