CODEWITHSUNDEEP
sqlmysql
sql beginner
sql beginner

Reputation: 145

Creating a range of numbers in an sql subquery

Is there a better way to generate a range of numbers in SQL than below? I'm using MySql.

SELECT tens.x + ones.x + 1
FROM
(SELECT 0 x UNION ALL
 SELECT 1 x UNION ALL
 SELECT 2 x UNION ALL
 ...
 SELECT 9 x ) ones

CROSS JOIN

(SELECT 0 x UNION ALL
 SELECT 10 x UNION ALL
 SELECT 20 x UNION ALL
 ...
 SELECT 90 x ) tens;

Upvotes: 0

Views: 2453

Answers (4)

priyanka.sarkar
priyanka.sarkar

Reputation: 26508

Why not loop? like

BEGIN
      DECLARE a INT Default 0 ;
      simple_loop: LOOP
         SET a=a+1;
         insert into mytable(id) values(a);
         IF a=1000 THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;

Modified from Mysql For Loop

Pardon me if the syntax is wrong as I am a pure SQL SERVER guy(:

Upvotes: 0

Frank Bollack
Frank Bollack

Reputation: 25186

PostgreSQL allows you to use:

select * from generate_series(2,4);
 generate_series
-----------------
               2
               3
               4

That is specific for the PostgresSQL engine. But it shouldn't be to hard to write a stored proedure for your data base.

Upvotes: 1

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

A common way to do that in Oracle is to abuse the rownum pseudocolumn:

select rownum from all_objects where rownum<=100;

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166416

Using Sql server 2005+ you can make use of CTEs

DECLARE @Start INT, @End INT

SELECT  @Start = 0, @End = 100000

;WITH Numbers AS (
        SELECT  @Start Num
        UNION ALL
        SELECT  Num + 1
        FROM    Numbers
        WHERE   Num < @End
)
SELECT  *
FROM    Numbers
OPTION (MAXRECURSION 0);

Upvotes: 1

Related Questions