This content has been marked as final.
Show 27 replies

1. Re: How to group numbers into ranges?
jeneesh Sep 19, 2007 10:46 AM (in response to 588171)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?
BluShadow Sep 19, 2007 11:20 AM (in response to 588171)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?
Rob van Wijk Sep 19, 2007 11:25 AM (in response to BluShadow)> 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?
BluShadow Sep 19, 2007 11:48 AM (in response to Rob van Wijk)Erm, but Janeesh's solution didn't output the correct results. ?!? 
5. Re: How to group numbers into ranges?
Rob van Wijk Sep 19, 2007 11:52 AM (in response to BluShadow)> 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
Regards,
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.
Rob. 
6. Re: How to group numbers into ranges?
Nicolas.Gasparotto Sep 19, 2007 11:57 AM (in response to BluShadow)You may not need MODEL or hierachical query here :SQL> with mytbl as
The problem now may come if some values are duplicated into the source table.
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, idrow_number() over (order by id) rid from mytbl)
14 group by rid;
MIN(ID) MAX(ID)
 
1 2
4 6
8 12
SQL>
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?
Nicolas.Gasparotto Sep 19, 2007 12:03 PM (in response to Nicolas.Gasparotto)
Maybe dense_rank() may workaround :The problem now may come if some values are duplicated into the source table
SQL> with mytbl as
Nicolas.
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, iddense_rank() over (order by id) rid from mytbl)
15 group by rid;
MIN(ID) MAX(ID)
 
1 2
4 6
8 12 
8. Re: How to group numbers into ranges?
BluShadow Sep 19, 2007 1:07 PM (in response to Rob van Wijk)> Erm, but Janeesh's solution didn't output the
Ah, my mistake. Yes it did confuse me.
correct results. ?!?
Jeneesh confusingly used another data set, but his
solution works: 
9. Re: How to group numbers into ranges?
cd_2 Sep 19, 2007 2:59 PM (in response to BluShadow)I'm sure someone will come along with a nice MODEL
I couldn't resist, although there are better alternatives already in this thread.
clause to do it, but just using basic connect by
technology...
C.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) ) ;

10. Re: How to group numbers into ranges?
588171 Sep 19, 2007 11:39 PM (in response to 588171)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?
588171 Feb 28, 2008 4:04 AM (in response to 588171)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?
121256 Feb 28, 2008 5:33 AM (in response to 588171)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?
627527 Mar 13, 2008 6:57 AM (in response to 588171)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?
121256 Mar 13, 2008 8:50 AM (in response to 627527)in my case the numbers are in range
i want to search the missing ranges between 1 and 10with 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