How to Generate a Number Sequence from 1 to 100 in SQL using DUAL?

Generating a Number Sequence in SQL: From 1 to 100

Question:

Can you provide a SQL query that generates a list of numbers from 1 to 100 using the DUAL table?

Answer:

Certainly! Here's a SQL query that can generate the desired sequence:

Select Rownum r
From dual
Connect By Rownum <= 100

In this query, we use the DUAL table, which is a built-in table in most SQL databases that always returns one row with no columns. We connect this table to itself using the CONNECT BY clause, which allows us to create a hierarchical tree-like structure. The ROWNUM pseudo-column is used to assign a unique number to each row, which we then use to create the sequence. By specifying that ROWNUM should be less than or equal to 100 in the CONNECT BY clause, we limit the query to return only the first 100 numbers.

This query should return the following output:

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

The above is the detailed content of How to Generate a Number Sequence from 1 to 100 in SQL using DUAL?. For more information, please follow other related articles on the PHP Chinese website!