Display Sequence of Numbers in SQL Using LEVEL

Last Updated : 30 Jan, 2019

The term LEVEL refers to a Pseudocolumn in Oracle which is used in a hierarchical query to identify the hierarchy level (parent->child) in numeric format. The LEVEL returns 1 for root row, 2 for child of root row and so on, as tree like structure. LEVEL must be used with CONNECT BY Clause. The CONNECT BY clause defines the hierarchical relationship between the parent rows and the child rows of the hierarchy. DUAL is a dummy table automatically generated by Oracle database along with data dictionary.

Example-1:

SELECT Level AS Sequence 
FROM Dual 
CONNECT BY Level <= 5 

Explanation:
Above query will execute Level having initial value 1 from dual, which is dummy table. ‘Sequence’ act as an ALias Name i.e., Temporary name of column. In query, condition will be checked and relationship created using Connect By between Level having value 1 and the specified condition. It will display values till it pass that specified condition.

Output:

Sequence
1
2
3
4
5

Start Sequence from specific number:
Level can be helpful to start a sequence of number from any particular initial value. Only need to add up one less than that value to Level with having condition in Connect By.

Syntax:

SELECT Level+(n-1) AS Alias Name
FROM Dual 
CONNECT BY Level <= 10 

Where n is initial specific number and Alias Name is temporary defined name of column.

Example-2:

SELECT Level+(6-1) AS Sequence 
FROM Dual 
CONNECT BY Level <= 10 

Explanation:
For display of sequence of numbers from 6 to 10. In above example, add up one less than 6 to Level and having condition till 10 using Connect By. It will execute and display range specified value as column name ‘Sequence’ from dummy table Dual.

Output:

Sequence
6
7
8
9
10

Example-3:

SELECT Level AS Sequence, Sysdate AS System_date   
FROM Dual 
CONNECT BY Level <= 5 

Explanation:
Above query will execute and display both Sequence of Numbers as well as Date. Level have Temporary column name Sequence and Date have column name System_date, as defined. It will retrieve data from dummy table called Dual. It will have 5 numbers of rows according to the condition specified in Connect By, from 1 to 5 in Sequence column and have Same date in all 5 rows in System_date column.

Output: