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

    How to group numbers into ranges?

    588171
      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?
          jeneesh
          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
            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
              > 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
                Erm, but Janeesh's solution didn't output the correct results. ?!?
                • 5. Re: How to group numbers into ranges?
                  Rob van Wijk
                  > 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?
                    Nicolas.Gasparotto
                    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?
                      Nicolas.Gasparotto
                      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?
                        BluShadow
                        > 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?
                          cd_2
                          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?
                            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
                              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
                                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
                                  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
                                    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