SQL select number ranges

Question

I found it quite hard to achieve a range of numbers as rows in MySQL.

For example the range 1-5 is achieved by:

SELECT 1 
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5

will result in:

1
2
3
4
5

for 0-99 I can cross join two 0-9 tables:

CREATE TABLE nums as
SELECT 0 as num
UNION
SELECT 1 
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6 
UNION
SELECT 7
UNION
SELECT 8
UNION
SELECT 9
;

Select n.num*10+nums.num v 
From nums n cross join nums

I'm tired of writing all these UNIONs and looking for a way to shrink the code.

Any ideas how to golf it (for example 0-1,000,000 range) in MySQL or any SQL syntax ?

Extra points are given for:

• single statement
• no procedures
• no variables
• no DDL statements
• only DQL statements

Answer 1

For SQL dialects that support recursive CTEs like sqlite, you can do something like the following:

WITH RECURSIVE f(x) AS
(
  SELECT 1 UNION ALL SELECT x + 1 FROM f LIMIT 1000000
)
SELECT x
FROM f;

This doesn't depend on any existing table and you can change the LIMIT clause as desired. I originally saw a variant of this on StackOverflow.

Answer 2

Similar to @BradC's method.

I used MS SQL, which has a table in [master] with a number range of -1 through 2048. You can use the BETWEEN operator to create your range.

SELECT DISTINCT(number)
FROM master..[spt_values] 
WHERE number BETWEEN 1 AND 5

If you want to golf this, you can do:

SELECT TOP 5 ROW_NUMBER()OVER(ORDER BY number)FROM master..spt_values

Answer 3

PostgreSQL, 35 bytes

PostgreSQL has this easy:

SELECT * FROM generate_series(1,5)

If you need it named:

SELECT num FROM generate_series(1,5)AS a(num)

You can also do this with timestamps. https://www.postgresql.org/docs/9.5/static/functions-srf.html

Answer 4

Great option from this post (found by @Arnauld):

SELECT id%1000001 as num
FROM <any_large_table>
GROUP BY num

For me - it's pretty much solves the challenge.

Answer 5

PostgreSQL specific

generate_series() generates a set, so you can user it not only in from clause, but anywhere where a set may occur:

psql=# select generate_series(10, 20, 3);
 generate_series 
-----------------
              10
              13
              16
              19
(4 rows)

You can also do operations directly on the set:

psql=# select 2000 + generate_series(10, 20, 3) * 2;
 ?column? 
----------
     2020
     2026
     2032
     2038
(4 rows)

If multiple sets have the same length, you can traverse them in parallel:

psql=# select generate_series(1, 3), generate_series(4, 6);
 generate_series | generate_series 
-----------------+-----------------
               1 |               4
               2 |               5
               3 |               6
(3 rows)

For sets with different lengths a Cartesian product is generated:

psql=# select generate_series(1, 3), generate_series(4, 5);
 generate_series | generate_series 
-----------------+-----------------
               1 |               4
               2 |               5
               3 |               4
               1 |               5
               2 |               4
               3 |               5
(6 rows)

But if you use them in from clause, you get Cartesian product for same length sets too:

psql=# select * from generate_series(1, 2), generate_series(3, 4) second;
 generate_series | second 
-----------------+--------
               1 |      3
               1 |      4
               2 |      3
               2 |      4
(4 rows)

It can also generate set of timestamps. For example you born on 2000-06-30 and want to know in which years you celebrated your birthday in a weekend:

psql=# select to_char(generate_series, 'YYYY - Day') from generate_series('2000-06-30', current_date, interval '1 year') where to_char(generate_series, 'D') in ('1', '7');
     to_char      
------------------
 2001 - Saturday 
 2002 - Sunday   
 2007 - Saturday 
 2012 - Saturday 
 2013 - Sunday   
(5 rows)

Answer 6

(These work in MS-SQL, not sure if they works for mySQL or other platforms.)

For smaller sets (ordered or non-ordered), use the VALUES constructor:

--Generates 0-9
SELECT a 
FROM(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))x(a)

(This works for anything, although strings can get pretty long with all the repeated single quotes.)

Then you can cross-multiply using a named CTE (common table expression) so you don't have to repeat it:

--Generates 0-999
WITH x AS(SELECT a FROM(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))x(a))
SELECT 100*x.a+10*y.a+z.a 
FROM x,x y,x z
ORDER BY 1

There are tons of other techniques out there, look for "SQL generating a number table", although most aren't optimized for golfing.

Answer 7

MS SQL has an undocumented system table in the master database called spt_values. Among other things, it contains a range of numbers from 0 to 2047:

--returns 0 to 2,047
SELECT number n 
FROM master..spt_values
WHERE TYPE='P'

Useful as a numbers table just by itself, but in a CTE you can get some big numbers pretty quickly:

--returns 0 to 4,194,304
WITH x AS(SELECT number n FROM master..spt_values WHERE TYPE='P')
SELECT 2048*x.a+*y.a
FROM x,x y
ORDER BY 1

Answer 8

One more option, this one specific to MS SQL 2016 and above:

SELECT value v
FROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16', ',')

I'll likely find this more handy for lists of strings, but I can see ways it will be useful with numbers as well.

Answer 9

T-SQL, 98 bytes

WITH H AS(SELECT 0i UNION ALL SELECT i+1FROM H WHERE i<99)SELECT H.i+1e4*A.i+B.i*1e2FROM H,H A,H B

• ✓ single statement
• ✓ no procedures
• ✓ no variables
• ✓ no DDL statements
• ✓ only DQL statements

Answer 10

Another for SQL Server...

WITH 
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),   -- 10
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),                             -- 100
    cte_Tally (n) AS (
        SELECT TOP (<how many ROWS do you want?>)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM
            cte_n2 a CROSS JOIN cte_n2 b                                                    -- 10,000
        )
SELECT 
    t.n
FROM
    cte_Tally t;