OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to generate sequence numbers between two numbers

January 20th, 2006 By Eddie Awad

One way:

HR@XE> define v_from = 20
HR@XE> define v_to = 26
HR@XE> select (lvl + &v_from - 1) myseq
  2  from (
  3      select *
  4      from (
  5          select level lvl
  6          from dual
  7          connect by level <= (&v_to - &v_from) + 1
  8          )
  9      )
 10  order by myseq;
old   1: select (lvl + &v_from - 1) myseq
new   1: select (lvl + 20 - 1) myseq
old   7:         connect by level <= (&v_to - &v_from) + 1
new   7:         connect by level <= (26 - 20) + 1

     MYSEQ
----------
        20
        21
        22
        23
        24
        25
        26

7 rows selected.

The above does not work in 8i or below.

I'm sure there are other ways to answer this question.

A similar technique is used when Tom Kyte answered the question: How can I bind an in-list.

8 Responses to “How to generate sequence numbers between two numbers”

  1. maceyah Says:
    SQL> create sequence seq1 minvalue 0 maxvalue 6 cycle cache 5;
    
    Sequence created.
    
    SQL> select seq1.nextval+20 from all_objects where rownum < 10;
    
    SEQ1.NEXTVAL+20
    ---------------
                 20
                 21
                 22
                 23
                 24
                 25
                 26
                 20
                 21
    
    9 rows selected.
    
  2. Eddie Awad Says:

    Maceyah, neat solution but is not very dynamic and it involves DDL.

    For example, using your method, to get the sequence numbers between 20 and 26 (i.e. 20, 21, 22, 23, 24, 25 and 26 — 7 ordered numbers in total), first you need to create a sequence with specific min and max values:

    create sequence myseq minvalue 1 maxvalue 7 cycle cache 6;
    
    select myseq.nextval+19 ---- (from value - 1)
      from all_objects 
     where rownum < =7 ---- (from - to + 1)
    

    Note that in this example, the minvalue and the maxvalue have to be 1 and 7 respectively (or 0 and 6), otherwise you may have numbers outside the range. So, if you have a different range you have to either create a new sequence or alter the existing one.

  3. Mihajlo Tekic Says:

    This is very nice solution it does not include any DDL activity.
    Since now, I used one table where I have only sequential numbers, which I increased always when I needed bigger range.

    Thanks,

  4. Laurent Schneider Says:

    I just could not resist telling this is a hack, because connect by without prior is illegal and should generate a loop.

    Using “from all_objects” is a “limiting move”, because it is a slow view with a limited number of objects (maybe 500, maybe 50000, you do not know).

    You can search my blog for examples, but my “favorite” approach is to say there is no such function. Why do you need it? Think of doing a table containing your elements, ex :

    create table t(n number);
    exec for i in 1..1000000 loop insert into t(n) values (i); end loop
    commit;
    select * from t where n between 20 and 26;
             N
    ----------
            20
            21
            22
            23
            24
            25
            26
    

    well, the PL/SQL part could be tuned, but the SELECT part is extremly fast

  5. Laurent Schneider Says:
    create table t(n number primary key);

    is better

  6. Eddie Awad Says:

    Laurent Schneider Says: Why do you need it?

    A co-worker of mine asked me: “Lets say I have a table with a numeric field (called x) and there are 3 records in that table and for those 3 records field x has a value of 22, 23, 25. I want to produce a report (sql select statement) that lists any number between 21 and 25 that is not on this table (ie the sql select statement should return 21 and 24 as a result set). The range (between 21 and 25) is a user input, and as a result can change with each run.”

    The above query can come in handy to solve such a problem:

    select *
        from (
            select (lvl + &v_from - 1) mynum
                from (
                    select *
                    from (
                        select level lvl
                        from dual
                        connect by level < = (&v_to - &v_from) + 1
                       )
                   )
           )
       where mynum not in (select x from t)
    
  7. gamyers Says:

    Like Laurent, I’m not happy with the CONNECT BY/LEVEL solution.

    This is a good alternative for 10G, and the MODEL clause is actually intended to generate rows.

    SELECT days
    FROM (select 20 days from dual) mnth
    MODEL
      DIMENSION BY (days)
      MEASURES (days v)
      RULES UPSERT
      (v[FOR days FROM 20 TO 26 INCREMENT 1] = 1)
    order by 1
    
  8. sreekumarvg Says:

    SQL> create sequence test_s minvalue 20 maxvalue 26 ;

    Sequence created.

    SQL>
    SQL>
    SQL>
    SQL> select test_s.nextval from all_objects where rownum

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question