Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL SELECT to get the first N positive integers

Tags:

sql

mysql

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?

like image 463
monn Avatar asked Sep 25 '09 07:09

monn


People also ask

How do I SELECT the first N column in SQL?

SQL requires that you name the columns you want, or else use the * wildcard. In relational theory, there is no concept of "first N columns" because columns have no implicit order.

How do I get just the first result in SQL?

To return only the first row that matches your SELECT query, you need to add the LIMIT clause to your SELECT statement. The LIMIT clause is used to control the number of rows returned by your query.

How do you SELECT nth value in SQL?

ROW_NUMBER (Window Function) ROW_NUMBER (Window Function) is a standard way of selecting the nth row of a table. It is supported by all the major databases like MySQL, SQL Server, Oracle, PostgreSQL, SQLite, etc.


1 Answers

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.

like image 182
Quassnoi Avatar answered Sep 26 '22 01:09

Quassnoi