Sql to Generate a List of Numbers from 1 to 100

How to display 1 to 100 numbers with using query

If you are using MySQL 8+, then your current syntax is not far off, and might even work:

WITH RECURSIVE cte (Number) AS (
    SELECT 1            -- base case returns 1
    UNION ALL
    SELECT Number + 1   -- recursive case returns 1 + previous value
    FROM cte
    WHERE Number < 100
)

SELECT Number
FROM cte
ORDER BY Number;

Recursive CTEs work by having a base case, which seeds one or more values, as well as a recursive case, which is an iteration on the previous values.

How to generate a list of number in SQL as it was a list of comprehension?

SQL Anywhere contains an sa_rowgenerator stored procedure, which can be used for this purpose. For example:

select row_num from sa_rowgenerator( 1, 100 )

returns a result set of 100 rows from 1 to 100 inclusive. A link to the documentation (for version 12.0.1) is here.

Disclaimer: I work for SAP/Sybase in the SQL Anywhere engineering.

Generate numbers 1 to 1000000 in MS Access using SQL

Yes, and it is not painfull - use a Cartesian query.

First, create a small query returning 10 records:

SELECT 
    DISTINCT Abs([id] Mod 10) AS N
FROM 
    MSysObjects;

Save it as Ten.

Then run this simple query:

SELECT 
    [Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000 AS Id
FROM 
    Ten AS Ten_0, 
    Ten AS Ten_1, 
    Ten AS Ten_2, 
    Ten AS Ten_3, 
    Ten AS Ten_4, 
    Ten AS Ten_5

which, in two seconds, will return Id from 0 to 999999.

SQL: Select a list of numbers from "nothing"

Thanks for all answers!
Following the discussion I realized that using a numbers table is not too complicated and works well and fast on both/many platforms:

CREATE TABLE integers (i integer);
INSERT INTO integers (i) VALUES (0);
INSERT INTO integers (i) VALUES (1);
...
INSERT INTO integers (i) VALUES (9);
SELECT (hundreds.i * 100) + (tens.i * 10) + units.i AS x
FROM integers AS units
  CROSS JOIN integers AS tens
  CROSS JOIN integers AS hundreds

You just create this table once and can use it whenever you need a range of numbers.

Generating a list of random numbers, summing to a fixed amount using SQL

While the @Squirrel answer is interesting but numbers here is more random
here is the code:

DECLARE @s INT=1,
        @k FLOAT=0,
        @final FLOAT=9.917,
        @sum FLOAT =0,
        @min FLOAT=1,
        @max FLOAT=9.917
        
BEGIN
    WHILE (@sum <> @final)
    BEGIN
        WHILE (@s <= 10)
        BEGIN
            
            SET @k =
            (
                SELECT ROUND(RAND(CHECKSUM(NEWID())) * (@max - @min) + @min,3)
            );
            PRINT (CONCAT('random: ',@k));
                    
            IF(@sum+@k <=@final)
                SET @sum+=@k;
            SET @max=@final-@sum;

            PRINT (CONCAT('computed sum: ',@k));
            IF(@max>1) SET @min=1 ELSE SET @min=0;

            IF(@sum=@final)
                BREAK;

            SET @s = @s + 1;
            SET @k = @k + 0;
        END;
        
         PRINT (CONCAT('final', @final))
         PRINT (CONCAT('sum', @sum))

         IF(@sum<>@final)--force stop if after 10 try the sum not match with final
          BEGIN
          PRINT(CONCAT('final random number:',@final-@sum))
          SET @sum=@final;
          END;

         SET @s=0;

         IF(@sum=@final)
            BEGIN
            PRINT('****************************DONE****************************')
            BREAK;
            END
    END;
    PRINT ('end');
END;

SQL server generate number from 1 to nth

Given how you phrase the question and the sample data you provide, I would be tempted to use a very bespoke approach for this:

with params as (
      select '1,2,3,4,5,6,7,8,9' as numbers,
             'x,x,x,x,x,x,x,x,x' as vals
     )
select l.*,
       left(numbers, interval * 2 - 1) as mult,
       replace(left(vals, interval * 2 - 1), 'x', (100 - cash) / interval) as val
from params cross join
     [like] l;

Of course, you might need to extend the strings in the CTE, if they are not long enough (and this might affect the arithmetic).

The advantage to this approach is speed. It should be pretty fast.

Note: you can also use replicate() rather than the vals.

vertica generate table with numbers & select prime numbers

You don't need recursive WITH for this.

First Step. You need to generate all numbers from 1 to N (let's say 1000). You can easily do this in Vertica using TIMESERIES. The following will generate all numbers from 1 to 1000:

WITH seq AS (
    SELECT ROW_NUMBER() OVER() AS num FROM (
        SELECT 1 FROM (
            SELECT date(0) + INTERVAL '1 second' AS se UNION ALL
            SELECT date(0) + INTERVAL '1000 seconds' AS se ) a
        TIMESERIES tm AS '1 second' OVER(ORDER BY se)
    ) b
)
SELECT num FROM seq ; 
 num  
------
    1
    2
    3
    4
  ...
  997
  998
  999
 1000

Second Step we just have to exclude non-prime numbers from the result set here above (see here, here and here):

WITH seq AS (
    SELECT ROW_NUMBER() OVER() AS num FROM (
        SELECT 1 FROM (
            SELECT date(0) + INTERVAL '1 second' AS se UNION ALL
            SELECT date(0) + INTERVAL '1000 seconds' AS se ) a
        TIMESERIES tm AS '1 second' OVER(ORDER BY se)
    ) b
)
SELECT num as prime 
FROM seq
WHERE num NOT IN (
    SELECT s1.num * s2.num
    FROM seq s1 CROSS JOIN seq s2
        WHERE s1.num BETWEEN 2 AND CEILING (SQRT (1000)) 
        AND s1.num <= s2.num
        AND s2.num * s1.num <= 1000
)
ORDER BY 1
;
 prime 
-------
     1
     2
     3
     5
     7
    11
   ...
   977
   983
   991
   997

---

Related Topics