Forum Stats

  • 3,757,562 Users
  • 2,251,245 Discussions
  • 7,869,867 Comments

Discussions

How to group numbers into ranges?

588171
588171 Member Posts: 11
edited Mar 14, 2008 6:28AM in SQL & PL/SQL
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!
«13

Comments

  • jeneesh
    jeneesh Member Posts: 7,168
    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
  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    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>
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    > 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.
  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    Erm, but Janeesh's solution didn't output the correct results. ?!?
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    > 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.
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited Sep 19, 2007 7:57AM
    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
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    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.
  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    > 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.
  • cd_2
    cd_2 Member Posts: 5,021
    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.
  • 588171
    588171 Member Posts: 11
    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?
This discussion has been closed.