15

What is a fast/readable way to SELECT a relation from "nothing" that contains a list of numbers. I want to define which numbers by setting a start and end value. I am using Postgres SQL and SQLite, and would be interested in generic solutions that will work on both/many platforms.

Desired output relation:

# x
  0
  1
  2
  3
  4

I know that I can SELECT a single row from "nothing": SELECT 0,1,2,3,4 But this selects the values as columns instead of rows and requires to specify all values in the query instead of only using my start and end values: 0 and 4.

In Postgres you have a special generate_series function for this case:

SELECT * FROM generate_series(0,4) x;

This works nicely but is non-standard. I can also imagine some complicated solutions using temporary tables, but I would like to have something generic AND simple like:

SELECT * FROM [0..4]

Maybe using the SEQUENCE statement or some magic combination of SELECT 0 and SELECT 4?

Juve
  • 10,584
  • 14
  • 63
  • 90
  • 1
    You might as well use numbers up to 1,000 I'd say -- put an index on the table perhaps. Otherwise, create views such as numbers_to_1000, numbers_to_1000000 to encapsulate those crossjoins. – David Aldridge Jul 16 '13 at 13:21
  • @Juve, could you accept some answer or post your answer and accept it? – Roman Pekar Aug 21 '13 at 06:54
  • When I started looking, I was astonished at how awkward this simple-seeming task is to achieve. Here's an article with some more possibilities (but it suggests that nothing tops GENERATE_SERIES() from PostgreSQL): https://blog.jooq.org/2013/11/19/how-to-create-a-range-from-1-to-10-in-sql/ – Dodecaphone Jun 25 '16 at 10:27
  • A lot of ideas aren't options for a read-only environment such as BigQuery. – Dodecaphone Jun 25 '16 at 10:28

7 Answers7

8

Well in SQL server (and PostgreSQL) I would use recursive common table expression: SQL Server, PostgreSQL

with recursive Numbers as (
    select 0 as Number
    union all
    select Number + 1
    from Numbers
    where Number < 4
)
select Number
from Numbers

SQL FIDDLE EXAMPLE

But, as far as I know, there's no WITH in SQLite.

So, the possible solutions could be

  • create a user defined function (this could be helpful)

  • create a table with numbers from 0 to max number you'll ever need, and then just select from it like this:

    select Number from Numbers where Number >= 0 and Number <= 4
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • The recursive CTE would work in Postgres as well (in fact it's the only "standard" way to do it - as nearly all modern DBMS support recursive CTEs nowadays) –  Jul 16 '13 at 10:25
  • true, I've just tried to help with SQLite, because he knows how to do it in PostgreSQL, but thank you, I'll edit the answer – Roman Pekar Jul 16 '13 at 11:00
  • 1
    While a recursive CTE would work in most modern DBMS, it has been proven as [a very inefficient way of generating a sequence](http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2). I think the numbers table is by far the best approach. – GarethD Jul 16 '13 at 11:28
  • @GarethD yes, in my production code I use numbers table for unknown ranges of numbers, dates – Roman Pekar Jul 16 '13 at 11:52
  • Thx, for the hint to the "numbers table", some further research then showed that you can cross join more ranges from this base table. (See my edit) – Juve Jul 16 '13 at 12:43
  • Recursive CTEs are soo much fun to write. Like puzzles. But they can be very slow to execute. – Paul Chernoch Jul 16 '13 at 14:51
  • Also be warned that you cannot ONLY execute the `with .. as ()` clause without the following `select`. It will cause the error ` Incorrect syntax near ')'` – user25064 Jul 10 '14 at 15:36
4

A simple way to do this in PostgreSQL and SQLite is as follows:

sqlite> select 1 union select 2 union select 3;
1
2
3

It should work in most RDBMS systems, but IIRC in Oracle you would have to use:

select 1 from dual union select 2 from dual union select 3 from dual;

But I don't have an Oracle DB to test it on.

gphil
  • 149
  • 2
2

Some other alternative (tried in postgres, but should be working with others)

select (0) as count union values (1),(2),(3),(4),(5)

Extending this sql with code will be straight forward.

mdev
  • 1,366
  • 17
  • 23
1

Thanks for all answers! Following the discussion I realized that using a numbers table is not too complicated and works well and fast on both/many platforms:

CREATE TABLE integers (i integer);
INSERT INTO integers (i) VALUES (0);
INSERT INTO integers (i) VALUES (1);
...
INSERT INTO integers (i) VALUES (9);
SELECT (hundreds.i * 100) + (tens.i * 10) + units.i AS x
FROM integers AS units
  CROSS JOIN integers AS tens
  CROSS JOIN integers AS hundreds

You just create this table once and can use it whenever you need a range of numbers.

Juve
  • 10,584
  • 14
  • 63
  • 90
0

This worked for me with MS SQL. It's a bit of a cheat in that it requires the table you're querying to have at least as many rows as the quantity of numbers you want returned. In my case I just wanted a list of five priorities that I could bind to a drop down list - but had to come from SQL given the way that other related UI elements worked:

SELECT WDPRIORITY, 
       CAST('Priority ' + LTRIM(STR(WDPRIORITY)) AS CHAR(16)) AS TXPRIORITY
  FROM (
         SELECT TOP 5 ROW_NUMBER() OVER(ORDER BY WDVALUE ASC) AS WDPRIORITY
           FROM VMIPRS
       ) AS VMPRIORITY
Tony Tullemans
  • 158
  • 2
  • 4
0

In case if somebody is looking for solution for MSSQL for known limited number of numbers:

SELECT x.x FROM (VALUES (1),(2),(3),(4)) AS x(x)
Dmitriy Grishin - dogrishin
  • 352
  • 4
  • 13
-2

If the number set is limited, I would go with:

SELECT
    *
FROM (VALUES (1),(2), (3))
user1528457
  • 1