# SQL select number ranges

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
• Not sure if this belongs in meta, or in dba.stackexchange.com or maybe in the tips for Golfing in SQL thread. – BradC Jun 30 '17 at 17:47
• To close voters: This is an on-topic challenge; questions that aren't challenges that are related to golfing code are considered on-topic tips questions. – hyper-neutrino Jun 30 '17 at 17:54
• I kinda like this answer from SO. Hackish at best, but you asked for a golfing solution after all. – Arnauld Jun 30 '17 at 18:01
• @Arnauld that's amazing! – Dimgold Jun 30 '17 at 18:05
• If “any SQL” includes PostgreSQL, see generate_series(). We have a couple of usage examples here. – manatwork Jun 30 '17 at 18:29

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.

• Excellent. Here's a golfed version that works in MS SQL: WITH t AS(SELECT 1n UNION ALL SELECT n+1FROM t WHERE n<36)SELECT n FROM t For different end points, just change the 1 and 36 to whatever you want. – BradC Jun 30 '17 at 20:41
• Oops, if you want more than 100 rows in MS SQL, you might need to add option (maxrecursion 0) to the end of my above statement , otherwise it errors out for recursion over 100. (Either set maxrecursion to a specific value, or to 0 to allow infinite). – BradC Jun 30 '17 at 20:48

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

• For golfing you save 2 bytes with WHERE number>0AND number<21 – BradC Jun 30 '17 at 19:24
• Why do you use distinct? Seems redundant. – Magic Octopus Urn Jun 30 '17 at 20:38
• @MagicOctopusUrn Because there are duplicate numbers in that table. – Oliver Jun 30 '17 at 20:40
• Yep you either need to use DISTINCT or use WHERE type='P'. Distinct is slightly shorter. – BradC Jun 30 '17 at 21:33
• @BradC, or SELECT DISTINCT(number+2)... WHERE number<19 – Peter Taylor Jun 30 '17 at 22:33

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

• I guess I'm moving to postgres – Dimgold Jun 30 '17 at 21:23

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.

• This seems to be relying on an existing table already having an id field populated through very large values. So pretty database specific, and you could miss a row if, say, someone deleted product ID = 4021. – BradC Jun 30 '17 at 18:47
• Yep, but it really good for relatively small ranges (1-7 for days, 1-12 for months etc...) – Dimgold Jun 30 '17 at 18:49

# 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)


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


(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.

• Would this work with a limit Y to make arbitrary ranges? – Rod Jun 30 '17 at 18:10
• @Rod In MS-SQL you'd have to use SELECT TOP 250 ... – BradC Jun 30 '17 at 18:13
• Oh, I didn't saw the MSSQL header =X – Rod Jun 30 '17 at 18:14
• doesn't work on MySQL, but still is useful :) – Dimgold Jun 30 '17 at 18:26

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.

# 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
• This is a nice tidy T-SQL version of langelgjm's answer. The exponents are a neat trick, as well. – BradC Sep 13 '19 at 20:39

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;