5 Replies Latest reply: Feb 10, 2013 7:38 AM by jeneesh

# Make it a range

11gR2 DB..

My data is as follows
``````with t as
(
select 1 sl_no from dual union all
select 2 from dual union all
select 3 from dual union all
select 7 from dual union all
select 8 from dual union all
select 9 from dual union all
select 11 from dual
)
select *
from t;

Requirement is to get it as a range:

from_no to_no
1         3
7         9
11       11``````
Any help is appreciated..
• ###### 1. Re: Make it a range
``````with t as (
select 1 sl_no from dual union all
select 2 from dual union all
select 3 from dual union all
select 7 from dual union all
select 8 from dual union all
select 9 from dual union all
select 11 from dual
),
t1 as (
select  sl_no,
case lag(sl_no) over(order by sl_no) + 1
when sl_no then 0
else 1
end start_of_group
from  t
),
t2 as (
select  sl_no,
sum(start_of_group) over(order by sl_no) grp
from  t1
)
select  min(sl_no) from_no,
max(sl_no) to_no
from  t2
group by grp
order by grp
/

FROM_NO      TO_NO
---------- ----------
1          3
7          9
11         11

SQL>``````
SY.
• ###### 2. Re: Make it a range
Actually, it could be done simpler (assuming sl_no is unique):
``````with t as (
select 1 sl_no from dual union all
select 2 from dual union all
select 3 from dual union all
select 7 from dual union all
select 8 from dual union all
select 9 from dual union all
select 11 from dual
)
select  sl_no from_no,
from  (
select  sl_no,
lag(sl_no) over(order by sl_no) prev_sl_no,
case lag(sl_no) over(order by sl_no) + 1
when sl_no then 0
else 1
end start_of_group
from  t
)
where start_of_group = 1
/

FROM_NO      TO_NO
---------- ----------
1          3
7          9
11         11

SQL>``````
SY.

Edited by: Solomon Yakobson on Feb 10, 2013 8:13 AM
• ###### 3. Re: Make it a range
Or..
``````with t as
(
select 1 sl_no from dual union all
select 2 from dual union all
select 3 from dual union all
select 7 from dual union all
select 8 from dual union all
select 9 from dual union all
select 11 from dual
)
select min(sl_no) from_sl_no,max(sl_no) to_sl_no
from (
select sl_no
from t
order by sl_no
)
group by sl_no-rownum
order by 1,2;

FROM_SL_NO TO_SL_NO
---------- --------
1        3
7        9
11       11 ``````
• ###### 4. Re: Make it a range
jeneesh wrote:
Or..
Only assuming there are no duplicates. Same applies to my second solution:
``````with t as
(
select 1 sl_no from dual union all
select 2 from dual union all
select 3 from dual union all
select 7 from dual union all
select 7 from dual union all
select 8 from dual union all
select 9 from dual union all
select 11 from dual
)
select min(sl_no) from_sl_no,max(sl_no) to_sl_no
from (
select sl_no
from t
order by sl_no
)
group by sl_no-rownum
order by 1,2
/

FROM_SL_NO   TO_SL_NO
---------- ----------
1          3
7          9
7         11

SQL> with t as
2  (
3  select 1 sl_no from dual union all
4  select 2 from dual union all
5  select 3 from dual union all
6  select 7 from dual union all
7  select 7 from dual union all
8  select 8 from dual union all
9  select 9 from dual union all
10  select 11 from dual
11  )
12  select  sl_no from_no,
13          lead(prev_sl_no,1,sl_no) over(order by sl_no) to_no
14    from  (
15           select  sl_no,
16                   lag(sl_no) over(order by sl_no) prev_sl_no,
17                   case lag(sl_no) over(order by sl_no) + 1
18                     when sl_no then 0
19                     else 1
20                   end start_of_group
21             from  t
22          )
23    where start_of_group = 1
24  /

FROM_NO      TO_NO
---------- ----------
1          3
7          7
7          9
11         11

SQL> with t as
2  (
3  select 1 sl_no from dual union all
4  select 2 from dual union all
5  select 3 from dual union all
6  select 7 from dual union all
7  select 7 from dual union all
8  select 8 from dual union all
9  select 9 from dual union all
10  select 11 from dual
11  ),
12      t1 as (
13             select  sl_no,
14                     case lag(sl_no) over(order by sl_no) + 1
15                       when sl_no then 0
16                       else 1
17                     end start_of_group
18               from  t
19            ),
20      t2 as (
21             select  sl_no,
22                     sum(start_of_group) over(order by sl_no) grp
23               from  t1
24            )
25  select  min(sl_no) from_no,
26          max(sl_no) to_no
27    from  t2
28    group by grp
29    order by grp
30  /

FROM_NO      TO_NO
---------- ----------
1          3
7          9
11         11

SQL>``````
SY.
• ###### 5. Re: Make it a range
Solomon Yakobson wrote:
jeneesh wrote:
Or..
Only assuming there are no duplicates. Same applies to my second solution:
Umm..There is no duplicate data in the sample given..

And the column name SL_NO indicates a unique number..

And otherwise also, does not require this "complex" query as you wrote, I feel..It is as SIMPLE as adding a DISTINCT..
``````with t as
(
select 1 sl_no from dual union all
select 2 from dual union all
select 3 from dual union all
select 7 from dual union all
select 7 from dual union all
select 8 from dual union all
select 9 from dual union all
select 11 from dual
)
select min(sl_no) from_sl_no,max(sl_no) to_sl_no
from (
select distinct sl_no
from t
order by sl_no
)
group by sl_no-rownum
order by 1,2;

FROM_SL_NO TO_SL_NO
---------- --------
1        3
7        9
11       11 ``````