SQL to generate a list of numbers from 1 to 100

Question

Using the DUAL table, how can I get a list of numbers from 1 to 100?

Anuradhapura

Is this homework? If not, the included requirement of "using the DUAL table" seems arbitrary and strange to me. – Lithoid

@Lithoid - using DUAL as a source is a well-known solution to the problem of generating rows, so its inclusion in the question is neither arbitrary nor strange. – Youngster

"it would be interesting to know why you want to do this" - testing unit conversion logic, or date logic that works for all hours (0-23) or quarter hours (0-95) or a similar exhaustive test. – Micronesian

Answer 1

Your question is difficult to understand, but if you want to select the numbers from 1 to 100, then this should do the trick:

Select Rownum r
From dual
Connect By Rownum <= 100

Rosenblatt

Answer 2

Another interesting solution in ORACLE PL/SQL:

    SELECT LEVEL n
      FROM DUAL
CONNECT BY LEVEL <= 100;

Clarindaclarine

This is plain Oracle SQL. It works fine outside a PL/SQL context. – Yasmineyasu

Answer 3

Using Oracle's sub query factory clause: "WITH", you can select numbers from 1 to 100:

WITH t(n) AS (
  SELECT 1 from dual
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT * FROM t;

Zaragoza

For the record, it's not an Oracle-specific clause, it is a standard CTE syntax which works the same way in both SQL Server and Oracle :-) – Rabassa

Postgres support similar recursive CTE – Zaragoza

Answer 4

You could use XMLTABLE:

SELECT rownum
FROM XMLTABLE('1 to 100');

-- alternatively(useful for generating range i.e. 10-20)
SELECT (COLUMN_VALUE).GETNUMBERVAL() AS NUM
FROM XMLTABLE('1 to 100');

DBFiddle Demo

Cumae

Answer 5

Do it the hard way. Use the awesome MODEL clause:

SELECT V
FROM DUAL
MODEL DIMENSION BY (0 R)
      MEASURES (0 V)
      RULES ITERATE (100) (
        V[ITERATION_NUMBER] = ITERATION_NUMBER + 1
      )
ORDER BY 1

Proof: http://sqlfiddle.com/#!4/d41d8/20837

Excellent

that's easy, why not pattern match... :-) – Thorough

@igr: I challenge you to actually provide that answer :-) – Excellent

what CV(R) means? Is it like current value? or sth? – Szombathely

@zygimantus, yes it's some "weird" syntax for the "current value" of R, meaning the value on the left side of the equation. For details, please refer to the Oracle MODEL white paper – Excellent

@LukasEder Can't we just use SELECT V FROM dual MODEL DIMENSION BY (0 AS R) MEASURES (0 AS V) RULES ITERATE (100) (V[ITERATION_NUMBER] = ITERATION_NUMBER+1) ORDER BY 1 DBFiddle Demo – Cumae

@lad2025: You're right of course, thanks a lot for the hint! – Excellent

Answer 6

If you want your integers to be bound between two integers (i.e. start with something other than 1), you can use something like this:

with bnd as (select 4 lo, 9 hi from dual)
select (select lo from bnd) - 1 + level r
from dual
connect by level <= (select hi-lo from bnd);

It gives:

4
5
6
7
8

Themis

Answer 7

Peter's answer is my favourite, too.

If you are looking for more details there is a quite good overview, IMO, here.
Especially interesting is to read the benchmarks.

Hawse

Answer 8

Using GROUP BY CUBE:

SELECT ROWNUM
FROM (SELECT 1 AS c FROM dual GROUP BY CUBE(1,1,1,1,1,1,1) ) sub
WHERE ROWNUM <=100;

Rextester Demo

Cumae

Answer 9

A variant of Peter's example, that demonstrates a way this could be used to generate all numbers between 0 and 99.

with digits as (
  select mod(rownum,10) as num 
  from   dual 
  connect by rownum <= 10
)
select a.num*10+b.num as num 
from   digits a
       ,digits b
order by num
;

Something like this becomes useful when you are doing batch identifier assignment, and looking for the items that have not yet been assigned.

For example, if you are selling bingo tickets, you may want to assign batches of 100 floor staff (guess how i used to fund raise for sports). As they sell a batch, they are given the next batch in sequence. However, people purchasing the tickets can select to purchase any tickets from the batch. The question may be asked, "what tickets have been sold".

In this case, we only have a partial, random, list of tickets that were returned within the given batch, and require a complete list of all possibilities to determine which we don't have.

with range as (
  select mod(rownum,100) as num 
  from   dual 
  connect by rownum <= 100
),
AllPossible as (
  select a.num*100+b.num as TicketNum
  from   batches a
         ,range b
  order by num
)
select TicketNum as TicketsSold
from   AllPossible
where  AllPossible.Ticket not in (select TicketNum from TicketsReturned)
;

Excuse the use of key words, I changed some variable names from a real world example.

... To demonstrate why something like this would be useful

Chivaree

Answer 10

I created an Oracle function that returns a table of numbers

CREATE OR REPLACE FUNCTION [schema].FN_TABLE_NUMBERS(
    NUMINI INTEGER,
    NUMFIN INTEGER,
    EXPONENCIAL INTEGER DEFAULT 0
) RETURN TBL_NUMBERS
IS
    NUMEROS TBL_NUMBERS;
    INDICE NUMBER;
BEGIN
    NUMEROS := TBL_NUMBERS();

    FOR I IN (
        WITH TABLA AS (SELECT NUMINI, NUMFIN FROM DUAL)
        SELECT NUMINI NUM FROM TABLA UNION ALL
        SELECT 
            (SELECT NUMINI FROM TABLA) + (LEVEL*TO_NUMBER('1E'||TO_CHAR(EXPONENCIAL))) NUM
        FROM DUAL
        CONNECT BY 
            (LEVEL*TO_NUMBER('1E'||TO_CHAR(EXPONENCIAL))) <= (SELECT NUMFIN-NUMINI FROM TABLA)
    ) LOOP
        NUMEROS.EXTEND;
        INDICE := NUMEROS.COUNT; 
        NUMEROS(INDICE):= i.NUM;
    END LOOP;

    RETURN NUMEROS;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
       RETURN NUMEROS;
  WHEN OTHERS THEN
       RETURN NUMEROS;
END;
/

Is necessary create a new data type:

CREATE OR REPLACE TYPE [schema]."TBL_NUMBERS" IS TABLE OF NUMBER;
/

Usage:

SELECT COLUMN_VALUE NUM FROM TABLE([schema].FN_TABLE_NUMBERS(1,10))--integers difference: 1;2;.......;10

And if you need decimals between numbers by exponencial notation:

SELECT COLUMN_VALUE NUM FROM TABLE([schema].FN_TABLE_NUMBERS(1,10,-1));--with 0.1 difference: 1;1.1;1.2;.......;10
SELECT COLUMN_VALUE NUM FROM TABLE([schema].FN_TABLE_NUMBERS(1,10,-2));--with 0.01 difference: 1;1.01;1.02;.......;10

Electromagnet

Answer 11

If you want to generate the list of numbers 1 - 100 you can use the cartesian product of {1,2,3,4,5,6,6,7,8,9,10} X {0,10,20,30,40,50,60,70,80,90} https://en.wikipedia.org/wiki/Cartesian_product Something along the lines of the following:

SELECT
    ones.num + tens.num
FROM
    (
        SELECT 1 num UNION ALL
        SELECT 2 num UNION ALL
        SELECT 3 num UNION ALL
        SELECT 4 num UNION ALL
        SELECT 5 num UNION ALL
        SELECT 6 num UNION ALL
        SELECT 7 num UNION ALL
        SELECT 8 num UNION ALL
        SELECT 9 num UNION ALL
        SELECT 10 num
    ) as ones
CROSS JOIN
 (
     SELECT 0 num UNION ALL
     SELECT 10 num UNION ALL
     SELECT 20 num UNION ALL
     SELECT 30 num UNION ALL
     SELECT 40 num UNION ALL
     SELECT 50 num UNION ALL
     SELECT 60 num UNION ALL
     SELECT 70 num UNION ALL
     SELECT 80 num UNION ALL
     SELECT 90 num
 ) as tens;

I'm not able to test this out on an oracle database, you can place the dual where it belongs but it should work.

Rhythm

Answer 12

WITH ones AS
    (
        SELECT 1 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 2 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 3 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 4 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 5 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 6 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 7 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 8 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 9 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 10 num  /* oracle add here FROM DUAL */ 
    ),
tens AS
(
    SELECT 10 * (num -1) AS num FROM ones
)
SELECT
    ones.num + tens.num
FROM ones, tens  -- cross join
ORDER BY 1;

Juratory

Answer 13

The simplest solution would be to use common table expression recursion logic as in below:

WITH cte_numbers
AS
(SELECT
        1 row_num
    UNION ALL
    SELECT
        row_num + 1
    FROM cte_numbers cn
    WHERE cn.row_num < 100)
SELECT
    row_num
FROM cte_numbers

Isobaric

Answer 14

SELECT * FROM `DUAL` WHERE id>0 AND id<101

The above query is written in SQL in the database.

Sublittoral

Which database is this for? – Micronesian