Select Sequential Numbers with Oracle SQL

Posted on December 6, 2011 By

Oracle provides the connect by clause that can be used to specify a relationship between parent rows and child rows.  This is used as part of the hierarchical query clause.  In general usage, the connect by clause uses the prior keyword to define the relationship.  When using the connect by clause, the level pseudocolumn is created.  This column is used to show the depth of the relationship.

In order to get a list of sequential numbers we will omit the prior keyword. In addition, we add a where clause that identifies the maximum number in the list.  In the below example we will return the number 1 through 365, each on a different row.

SELECT level lev FROM   dual CONNECT BY LEVEL<=365;

If you need a range of numbers, starting higher than 1 you can just add a where clause as shown below.

SELECT level lev FROM   dual
where level>5
CONNECT BY LEVEL<=365;

FYI: Oracle has optimized queries against the dual table and they do not require I/O operations.

 

SQL     , , , ,