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

User · Answer

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

User · Answer

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  lt  100    Rextester Demo

User · Answer

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  lt    select hi-lo from bnd     It gives   4 5 6 7 8

User · Answer

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  lt  100   SELECT   FROM t

User · Answer

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

User · Answer

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

User · Answer

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  lt   100

User · Answer

Another interesting solution in ORACLE PL SQL       SELECT LEVEL n       FROM DUAL CONNECT BY LEVEL  lt   100

User · Answer

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     lt    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

User · Answer

SELECT   FROM  DUAL  WHERE id gt 0 AND id lt 101   The above query is written in SQL in the database

User · Answer

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  lt   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  lt   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

[sql] SQL to generate a list of numbers from 1 to 100

Examples related to sql

Examples related to oracle

Examples related to plsql