How to Generate a Numerical Sequence in SQL Using the DUAL Table?

How to Generate a Sequence of Numbers in SQL

Generating a sequential list of numbers is a common task in data manipulation. One common way to do this in SQL is by using the DUAL table. The DUAL table is a virtual table that contains exactly one row with one column, which is always named DUMMY. To query this table, we can use the following syntax:

SELECT * FROM DUAL;

Now, to generate a sequence of numbers from 1 to 100 using the DUAL table, we can use the CONNECT BY clause. The CONNECT BY clause allows us to perform recursive queries, which are useful for generating sequences of numbers, among other things.

SELECT ROW_NUMBER() OVER (ORDER BY LEVEL) r
FROM dual
CONNECT BY LEVEL <= 100;

In this query, the ROW_NUMBER() function is used to generate the sequence of numbers. The OVER (ORDER BY LEVEL) clause specifies that the numbers should be generated in ascending order based on the LEVEL pseudocolumn. The LEVEL pseudocolumn represents the level of each row in the hierarchy created by the CONNECT BY clause. In this case, all rows are at the same level, so the LEVEL column simply contains the values 1 to 100.

| r |
|---|---|
| 1 |
| 2 |
| 3 |
| . |
| 99 |
| 100 |

The above is the detailed content of How to Generate a Numerical Sequence in SQL Using the DUAL Table?. For more information, please follow other related articles on the PHP Chinese website!