CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
Santosh
Santosh

Reputation: 2505

Generate random sequence in Oracle

I am trying to generate random numbers into 2 columns where the first column is from_number and second column in to_number.

My query looks as following

select to_char(5001 + (level-1)),
       to_char(5005 + (level-1))          
  from dual 
connect by level <= 100;

My output is for the above query is:

5001    5005
5002    5006
5003    5007
5004    5008
5005    5009

and so on...

But my output should be like following:

5001    5005
5006    5010
5011    5015
5016    5020

and so on...

The second-row 'from_number' should be the first-row 'to_number'+1

How can achieve this?

Thanks in advance.

Upvotes: 0

Views: 471

Answers (1)

Utsav
Utsav

Reputation: 8093

Note that what you are using here is not a random sequence. It is a fixed sequence. To know how to generate random number read this

Now coming back to your question, you can do it by playing a little with level. Note I reduced the <=100 to <=20 as we are using a multiplier of 5 so the maximum value you will get is 5005 + 20*5 - 5 = 6000. Change it back to <=100 if you want total of 100 rows.

select 
 to_char(5001 + (level*5) - 5 ),
 to_char(5005 + (level*5) - 5)          
from dual 
connect by level <= 20;

Upvotes: 4

Related Questions