19
\$\begingroup\$

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
\$\endgroup\$
  • 2
    \$\begingroup\$ Not sure if this belongs in meta, or in dba.stackexchange.com or maybe in the tips for Golfing in SQL thread. \$\endgroup\$ – BradC Jun 30 '17 at 17:47
  • 8
    \$\begingroup\$ 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. \$\endgroup\$ – HyperNeutrino Jun 30 '17 at 17:54
  • 3
    \$\begingroup\$ I kinda like this answer from SO. Hackish at best, but you asked for a golfing solution after all. \$\endgroup\$ – Arnauld Jun 30 '17 at 18:01
  • \$\begingroup\$ @Arnauld that's amazing! \$\endgroup\$ – Dimgold Jun 30 '17 at 18:05
  • 2
    \$\begingroup\$ If “any SQL” includes PostgreSQL, see generate_series(). We have a couple of usage examples here. \$\endgroup\$ – manatwork Jun 30 '17 at 18:29

10 Answers 10

9
\$\begingroup\$

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.

\$\endgroup\$
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – BradC Jun 30 '17 at 20:41
  • 1
    \$\begingroup\$ 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). \$\endgroup\$ – BradC Jun 30 '17 at 20:48
6
\$\begingroup\$

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

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

\$\endgroup\$
  • 2
    \$\begingroup\$ I guess I'm moving to postgres \$\endgroup\$ – Dimgold Jun 30 '17 at 21:23
4
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – BradC Jun 30 '17 at 18:47
  • \$\begingroup\$ Yep, but it really good for relatively small ranges (1-7 for days, 1-12 for months etc...) \$\endgroup\$ – Dimgold Jun 30 '17 at 18:49
4
\$\begingroup\$

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)
\$\endgroup\$
3
\$\begingroup\$

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
\$\endgroup\$
3
\$\begingroup\$

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

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

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.

\$\endgroup\$
2
\$\begingroup\$

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

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;
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.