Skip to content
Related Articles

Related Articles

Improve Article

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:

SequenceSystem_date
12019-01-05
22019-01-05
32019-01-05
42019-01-05
52019-01-05

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.




My Personal Notes arrow_drop_up
Recommended Articles
Page :