Display Sequence of Numbers in SQL Using LEVEL

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:

filter_none

edit
close

play_arrow

link
brightness_4
code

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

chevron_right


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:

filter_none

edit
close

play_arrow

link
brightness_4
code

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

chevron_right


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

Example-2:

filter_none

edit
close

play_arrow

link
brightness_4
code

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

chevron_right


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:

filter_none

edit
close

play_arrow

link
brightness_4
code

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

chevron_right


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:

Sequence System_date
1 2019-01-05
2 2019-01-05
3 2019-01-05
4 2019-01-05
5 2019-01-05


My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.