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

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 2

Take a look at the the followhing 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 3

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 4

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 5

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 6

Weird solution, but...

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