SQL SELECT to get the first N positive integers

Question

I need to get a result set containing the first N positive integers. Is it possible to use only standard SQL SELECT statement to get them (without any count table provided)?

If it's not possible, is there any specific MySQL way to achieve this?

Answer 1

Seems that what you want is a dummy rowset.

In MySQL, it's impossible without having a table.

Most major systems provide a way to do it:

• In Oracle:

  SELECT  level
  FROM    dual
  CONNECT BY
          level <= 10
  

• In SQL Server:

  WITH    q AS
          (
          SELECT  1 AS num
          UNION ALL
          SELECT  num + 1
          FROM    q
          WHERE   num < 10
          )
  SELECT  *
  FROM    q
  

• In PostgreSQL:

  SELECT  num
  FROM    generate_series(1, 10) num
  

MySQL lacks something like this and this is a serious drawback.

I wrote a simple script to generate test data for the sample tables in my blog posts, maybe it will be of use:

CREATE TABLE filler (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$

You call the procedure and the table gets filled with the numbers.

You can reuse it during the duration of the session.

Answer 2

A possible solution (admittedly not very elegant) is to use any table with a sufficiently large number of records.

For the first 10 integers (using the mysql.help_relation, but any table would do), you could use the following query:

SELECT  @N := @N +1 AS integers 
FROM mysql.help_relation , (SELECT @N:=0) dum LIMIT 10;

This could also be placed in a function taking Min and Max.

Answer 3

Weird solution, but...

SELECT 1 UNION SELECT 2 UNION SELECT 3....

Answer 4

The sequence I propose allows the programmer to execute the following query :

select value from sequence where value>=15 and value<100;

And to obtain the expected results : the sequence of integers between 15 (inclusive) and 100 (exclusive).

If that's what you want, you'll have to create the two following views, views that you'll declare only once :

create view digits as select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9;

create view sequence as select u.n+t.n*10+h.n*100 as value from digits as u cross join digits as t cross join digits as h;

This way you have your sequence with an intuitive SELECT...

Hope it helps.

Answer 5

Assuming you mean retrieve them from a table, here N is 10, assuming intcolumn is the column with numbers in it.

SELECT intcolumn FROM numbers WHERE intcolumn > 0 LIMIT 10

Edit: In case you were actually looking to get the mathematical set of positive numbers without a table, I would reconsider, it can be intensive (depending on the implementation). Commonly accepted practice seems to be to create a lookup table full of numbers, and then use the above query.

Answer 6

This may help

To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i)). For example, to obtain a random integer in the range the range 7 <= R < 12, you could use the following statement:

SELECT FLOOR(7 + (RAND() * 5));

Answer 7

If you know that N is limited (and usually it is), you can use a construction such as:

select (a.digit + (10 * b.digit) + (100 * c.digit) + (1000 * d.digit) + (10000 * e.digit) + (100000 * f.digit)) as n
    from (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as e
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as f;

which will generate the first million numbers. If you need only the positive numbers, simply add + 1 to the expression.

Note that in MySQL in particular, the results may not be sorted. You need to append order by n to the end if you need ordered numbers. This will increase the execution time dramatically, though (on my machine, it jumped up from 5 ms to 500 ms).

For simple queries, here's a query for just the first 10000 numbers:

select (a.digit + (10 * b.digit) + (100 * c.digit) + (1000 * d.digit)) as n
    from (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d;

This answer is adapted from the following query that returns a date range: https://stackoverflow.com/a/2157776/2948

Answer 8

Take a look at the following SO questions:

• How to generate a range of numbers in Mysql
• generate an integer sequence in MySQL

Edit:

Another aproach is to create a stored procedure that does that for you. PostgreSQL contains a function generate_series(start, stop) that does what you want.

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

I'm not familar with MySQL but somthing like that should be easy to implement, if you are okay with SPs. This site shows an implemetation.

Answer 9

I' pretty sure that you cannot do it, if I understand your question correctly.

As I understand your question, you want the list, from a single SQL statement, without having to reference a specific table?

I'm pretty sure that it is not possible in any SQL dialect. If you were to get a sequentially incrementing number along with the results of another query, then that would be possible (depending on SQL dialect, on mssql it would be rownumber(), but I don't know how in MySql, but it's probably there)

But that's not what I hear you ask?

Answer 10

If your database supports analytic windowing functions the following is simple works really well:

SELECT  row_number() over (partition by 1 order by 1) numbers
FROM SOME_TABLE
LIMIT 2700;

This statement returns a set of numbers from 1 to 2700.