SQL SELECT to get the first N positive integers

by

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.

More Related Contents:

  1. Join vs. sub-query
  2. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  3. Natural Sort in MySQL
  4. MySQL get row position in ORDER BY
  5. MySQL Results as comma separated list
  6. MySQL unknown column in ON clause
  7. MySQL not using indexes with WHERE IN clause?
  8. SQL: Find the max record per group [duplicate]
  9. Group by month and year in MySQL
  10. calculate a sum of type time using sql
  11. MySQL: Transactions vs Locking Tables
  12. Remove duplicates using only a MySQL query?
  13. Group by date only on a Datetime column
  14. Finding the next available id in MySQL
  15. How exactly does using OR in a MySQL statement differ with/without parentheses?
  16. MySQL: How to copy rows, but change a few fields?
  17. How to renumber primary index
  18. Query that ignore the spaces
  19. MySQL select where column is not empty
  20. Hamming distance on binary strings in SQL
  21. Is it possible to have a default parameter for a mysql stored procedure?
  22. Renaming foreign-key columns in MySQL
  23. How to convert from varbinary to char/varchar in mysql
  24. MySQL GROUP BY behavior
  25. How to get rid of “Error 1329: No data – zero rows fetched, selected, or processed”
  26. Update multiple rows with one query?
  27. MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) – Unexpected result
  28. MySql bulk load command line tool
  29. ALTER TABLE without locking the table?
  30. MySQL: Typecasting NULL to 0

Leave a Comment