SQL to Generate a Sequence Number

Your rating: None Average: 2.8 (34 votes)

Generating a sequence number isn't that hard. All what you need to type is the following:

CREATE sequence my_seq start WITH 1;

Select from dual to increment your sequence number by one.

SELECT my_seq.NEXTVAL FROM dual;

What if you want to create a sequence that increments by 3?

SQL> CREATE sequence seq_3 start WITH 1 increment BY 3;

Sequence created.

SQL> SELECT seq_3.NEXTVAL FROM dual;

   NEXTVAL
----------
         1

SQL> SELECT seq_3.NEXTVAL FROM dual;

   NEXTVAL
----------
         4

SQL> SELECT seq_3.NEXTVAL FROM dual;

   NEXTVAL
----------
         7

Now, let's make it a little harder. what if you want to create a sequence # that is not divisible by 5?! The most straight forward way of doing this is to use the FLOOR function while selecting from dual.

SQL> CREATE sequence seq start WITH 1;

Sequence created.

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5
---------------
              1

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5
---------------
              2

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5
---------------
              3

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5
---------------
              4

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL - 1)/4) not_divisible_5 FROM dual;

NOT_DIVISIBLE_5
---------------
              6
-- Notice how the number 5 is missing from the results as it is divisible by 5.

Here is a quick example on how the FLOOR function works to help you understand it better.
The following example returns the largest integer equal to or less than 18.6:
SELECT FLOOR(18.6) "Floor" FROM DUAL;

     Floor
----------
        18

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
sasanka's picture
sasanka (not verified)
Re: SQL to Generate a Sequence Number

Can any one tell me how to generate number sequence by just a select query with dual table and without creating a Sequence??????

ravi's picture
ravi (not verified)
Re: SQL to Generate a Sequence Number

can anybody tell me how to generate sequence in netezza server..??

David Soussan's picture
David Soussan (not verified)
Re: SQL to Generate a Sequence Number

Yes, and with a standard sql that works on any server.

The method is to use a self-join with grouping to generate the sequence number.

SELECT t1.userid, COUNT(t1.tableid) AS sequence, t1.tableid, t1.tableid > = t2.tableid AS flg
FROM table t1 INNER JOIN table t2 ON t1.userid = t2.userid
GROUP BY t1.userid, t1.tableid, flg
HAVING flg = TRUE

What this does is to join each row to itself and all those within the same sub-set, in this case ‘userid’, that were inserted before it and then count them. So the first entry for each user is only joined to itself = 1, the second is joined to itself and the first = 2, third to itself and the first two = 3, etc.

To reverse the sequence simply reverse the selection

t1.tableid < = t2.tableid AS flg

This example uses the userid as the sub-set definition, but it could be any field you want.

You can find a fuller discussion of this solution with examples of its' use at mu blog davidsoussan.co.uk

logan's picture
logan (not verified)
NEED HELP!!!! SQL to update a Sequence Number

i have a column containing 4 digit numbers ...such as 8841,8853,8823,9951,8841..etc column contain repeat values too ...
i need to convert them into 7 digit...and want to assign them 3 more digits
eg
8841 gets converted into 8841001 next
8841 gets converted into 8841002 next
8841 gets converted into 8841003 next
8853 gets converted into 8853001 next
9951 gets converted into 9951001 next
9951 gets converted into 9951002 and so on...
kindly help....i am a noob in sql...plz

Guest's picture
Guest (not verified)
Re: SQL to Generate a Sequence Number

Nice post Lepa......................

Regards,

Paritosh

Lars's picture
Lars (not verified)
Re: SQL to Generate a Sequence Number

You could simplify the algorithm :

SQL> SELECT seq.NEXTVAL + floor((seq.NEXTVAL)/5) not_divisible_5 FROM dual;

it is more intuitive as the 'not required' factor becomes the denominator

Guest's picture
Guest (not verified)
Re: SQL to Generate a Sequence Number

select level
from dual
connect by level <= 10 ;

Guest's picture
Guest (not verified)
Re: SQL to Generate a Sequence Number

Anyone can help me how to add the sequence in the 1 query which the query have select and join other table, but for the sequent, it was the only one table. How to join it if there is no synonym table for sequent with the main table selected.

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
6 + 9 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.