SQL Select to Get the First N Positive Integers

SQL SELECT to get the first N positive integers

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.

Select the first N digits of an integer

Use the LEFT function.

SELECT activityId, LEFT(activityId, 4) AS projectnumber
FROM Activities

Or the SUBSTRING function.

SELECT activityId, SUBSTRING (activityId, 1, 4) AS projectnumber
FROM Activities

And if you want too include your subprojectnumber

LEFT and RIGHT functions.

SELECT activityId, LEFT(activityId, 4) AS projectnumber, RIGHT(activityId, 4) AS subprojectnumber
FROM Activities

SUBSTRING function.

SELECT activityId, SUBSTRING (activityId, 1, 4) AS projectnumber, SUBSTRING(activityId, 5, 4) AS subprojectnumber
FROM Activities

How to get first n numbers from float

I found the solution. The problem was that SQL Server uses the exponential representation of floats. To resolve it you need to first convert float to BigInt and then use Left on it.

Example:

Select * from A where Left(Cast(float_value as BigInt), 4) = xxxx

Generating a range of numbers in MySQL

If you need the records in a table and you want to avoid concurrency issues, here's how to do it.

First you create a table in which to store your records

CREATE TABLE `incr` (
  `Id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Secondly create a stored procedure like this:

DELIMITER ;;
CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
  WHILE v1 > 0 DO
    INSERT incr VALUES (NULL);
    SET v1 = v1 - 1;
  END WHILE;
END;;
DELIMITER ;

Lastly call the SP:

CALL dowhile();
SELECT * FROM incr;

Result

Generate an integer sequence in MySQL

There is no sequence number generator (CREATE SEQUENCE) in MySQL. Closest thing is AUTO_INCREMENT, which can help you construct the table.

SQL - Find the lowest unused number

Assuming your number start from 1 always below query will give unused number

select min(rank) as Num from 
(select num,@curRank1 := @curRank1 + 1 AS rank from (SELECT num1 as num FROM t1 
UNION
SELECT num2 as num FROM t2) a1, (SELECT @curRank1 := 0) r ORDER BY num ASC) tab where num != rank;`

Sql syntax to insert every thousandth positive integer, starting from 1 and up until 1 million?

insert into dummy
   ( thousand )
select
     PreQuery.thousand 
  from
    (  select
             @sqlvar thousand,
             @sqlvar := @sqlvar + 1000
          from
             AnyTableWithAtLeast1000Records,
             ( select @sqlvar := 1 ) sqlvars
          limit 1000 ) PreQuery

You can insert from a select statement. Using MySQL Variables, start with 1. Then, join to ANY table in your system that may have 1000 (or more) records just to generate a row. Even though not getting any actual column from such table, we just need it for the record position. Then the @sqlvar starts at 1 and is returned in column named thousand. Then, immediately add 1000 to it for the next record in the "AnyTable..."

SQL Select to Get the First N Positive Integers