4

In my case, using sql anywhere (sybase).

Something similar to haskell. [1..100].

I don't know how to generate a random simple list of 1 up to 100.

I only could do it doing:

select 1
union 
select 2
union
select 3

Google did not provide any sample, I suspect this feature does not exist.

Ismael
  • 2,330
  • 1
  • 25
  • 37
  • possible duplicate of [SQL Select 'n' records without a Table](http://stackoverflow.com/questions/6533524/sql-select-n-records-without-a-table) – Barry Kaye Jan 15 '13 at 14:04
  • @MarkByers feel free to edit the post to add tag, i cannot see what tags it should have. – Ismael Jan 15 '13 at 15:35

4 Answers4

3

I find the easiest way for a short list is something like:

select t.*
from (select row_number() over (order by (select NULL)) as num
      from Information_Schema.columns
     ) t
where num <= 100

The columns table usually has at least 100 rows. Other tables can also be used.

For large numbers, something like the following:

with digits as (
      select 0 as dig union all select 1 union all select 2 union all select 3 union all select 4 union all
      select 5 union all select 6 union all select 7 union all select 8 union all select 9
),
     numbers as
      select d1.dig*10+d2.dig
      from digits d1 cross join
           digits d2
)
 . . .
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
3

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.

Graeme Perrow
  • 56,086
  • 21
  • 82
  • 121
  • Finally! A solution and not a "ugly workaround". I will check other stored procedures better, i know only the basics functions from sql anywhere. – Ismael Jan 16 '13 at 12:07
1
Oracle queries - use any number to start/end:

 SELECT 99 + ROWNUM
   FROM dual       
 CONNECT BY ROWNUM <= 100 + 1
/

SELECT 99 + LEVEL
  FROM dual       
CONNECT BY LEVEL <= 100 + 1
/
Art
  • 5,616
  • 1
  • 20
  • 22
0

with DD as ( select 1 as F union all select 1 + F from DD where F < 100 ) select * from DD

Yrk
  • 1