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

    Make it a range

    887479
      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
          Solomon Yakobson
          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
            Solomon Yakobson
            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,
                    lead(prev_sl_no,1,sl_no) over(order by sl_no) to_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
              jeneesh
              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
                Solomon Yakobson
                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
                  jeneesh
                  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