Generate Series or List of Numbers

To generate a list of numbers or series in Oracle or SQL Server, we need to use recursive CTE, if we call the CTE within the CTE then it is called recursive CTE

 

SQL Server:

 

; WITH REC_CTE (ROW_NUM) AS (

--create first record, if we want to start the series from 10 then change it from 1 to 10

SELECT 1 AS ROW_NUM

UNION ALL

--calling REC_CTE within REC_CTE and add value 1 to get next number

SELECT ROW_NUM+1 FROM REC_CTE

--limit the number of rows

WHERE ROW_NUM<100

)

--now select the CTE to get the list of values

SELECT * FROM REC_CTE;

 

Oracle 11g version 2 and higher:

 

WITH REC_CTE (ROW_NUM) AS (

--create first record, if we want to start the series from 10 then change it from 1 to 10

SELECT 1 AS ROW_NUM FROM DUAL

UNION ALL

--calling REC_CTE within REC_CTE and add value 1 to get next number

SELECT ROW_NUM+1 FROM REC_CTE

--limit the number of rows to 100

WHERE ROW_NUM<100

)

--now select the CTE to get the list of values

SELECT * FROM REC_CTE;

 

Oracle:

Oracle provides a set of hierarchy functions such as START WITH, LEVEL, PRIOR, CONNECT BY, CONNECT_BY_ROOT, see this link for more info

 

Example1:

            --below script will retrieve first hundred numbers

SELECT ROWNUM FROM DUAL

--limit the number of rows to 100

CONNECT BY ROWNUM <= 100;

 

Example2:

            --we can use level if we want to create a series from number other than 1

            SELECT LEVEL LVL FROM DUAL

            -- if we want to start the series from 10 then change it from 3 to 10

WHERE LEVEL >=3

--limit the number of rows to 100

CONNECT BY LEVEL<100

 

If you need to check the version of oracle, use below script

 

            SELECT * FROM V$VERSION

WHERE BANNER LIKE 'ORACLE%';

 

Above we have added 1 when we called the CTE with in CTE, if we add 2 then we will get even number series and so on.

 

Thank you for reading this article, check out my other SQL Server vs. Oracle posts.