# How to Create a Range From 1 to 10 in SQL

How do you create a range from 1 to 10 in SQL? Have you ever thought about it? This is such an easy problem to solve in any imperative language, it’s ridiculous. Take Java (or C, whatever) for instance:

```for (int i = 1; i <= 10; i++)
System.out.println(i);
```

This was easy, right? Things even look more lean when using functional programming. Take Scala, for instance:

```(1 to 10) foreach { t => println(t) }
```

We could fill about 25 pages about various ways to do the above in Scala, agreeing on how awesome Scala is (or what hipsters we are).

### But how to create a range in SQL?

… And we’ll exclude using stored procedures, because that would be no fun. In SQL, the data source we’re operating on are tables. If we want a range from 1 to 10, we’d probably need a table containing exactly those ten values. Here are a couple of good, bad, and ugly options of doing precisely that in SQL. OK, they’re mostly bad and ugly.

By creating a table

The dumbest way to do this would be to create an actual temporary table just for that purpose:

```CREATE TABLE "1 to 10" AS
SELECT 1 value FROM DUAL UNION ALL
SELECT 2       FROM DUAL UNION ALL
SELECT 3       FROM DUAL UNION ALL
SELECT 4       FROM DUAL UNION ALL
SELECT 5       FROM DUAL UNION ALL
SELECT 6       FROM DUAL UNION ALL
SELECT 7       FROM DUAL UNION ALL
SELECT 8       FROM DUAL UNION ALL
SELECT 9       FROM DUAL UNION ALL
SELECT 10      FROM DUAL
```

This table can then be used in any type of select. Now that’s pretty dumb but straightforward, right? I mean, how many actual records are you going to put in there?

By using a VALUES() table constructor

This solution isn’t that much better. You can create a derived table and manually add the values from 1 to 10 to that derived table using the `VALUES()` table constructor. In SQL Server, you could write:

```SELECT V
FROM (
VALUES (1), (2), (3), (4), (5),
(6), (7), (8), (9), (10)
) [1 to 10](V)
```

By creating enough self-joins of a sufficent number of values

Another “dumb”, yet a bit more generic solution would be to create only a certain amount of constant values in a table, view or CTE (e.g. two) and then self join that table enough times to reach the desired range length (e.g. four times). The following example will produce values from 1 to 10, “easily”:

```WITH T(V) AS (
SELECT 0 FROM DUAL UNION ALL
SELECT 1 FROM DUAL
)
SELECT V FROM (
SELECT 1        +
T1.V +
2 * T2.V +
4 * T3.V +
8 * T4.V V
FROM T T1, T T2, T T3, T T4
)
WHERE V <= 10
ORDER BY V
```

By using grouping sets

Another way to generate large tables is by using grouping sets, or more specifically by using the `CUBE()` function. This works much in a similar way as the previous example when self-joining a table with two records:

```SELECT ROWNUM FROM (
SELECT 1
FROM DUAL
GROUP BY CUBE(1, 2, 3, 4)
)
WHERE ROWNUM <= 10
```

By just taking random records from a “large enough” table

In Oracle, you could probably use `ALL_OBJECTs`. If you’re only counting to 10, you’ll certainly get enough results from that table:

```SELECT ROWNUM FROM ALL_OBJECTS
WHERE ROWNUM <= 10
```

What’s so “awesome” about this solution is that you can cross join that table several times to be sure to get enough values:

```SELECT ROWNUM
FROM ALL_OBJECTS, ALL_OBJECTS,
ALL_OBJECTS, ALL_OBJECTS
WHERE ROWNUM <= 10
```

OK. Just kidding. Don’t actually do that. Or if you do, don’t blame me if your productive system runs low on memory.

By using the awesome PostgreSQL GENERATE_SERIES() function

Incredibly, this isn’t part of the SQL standard. Neither is it available in most databases but PostgreSQL, which has the `GENERATE_SERIES()` function. This is much like Scala’s range notation: `(1 to 10)`

```SELECT * FROM GENERATE_SERIES(1, 10)
```

By using CONNECT BY

If you’re using Oracle, then there’s a really easy way to create such a table using the `CONNECT BY` clause, which is almost as convenient as PostgreSQL’s `GENERATE_SERIES()` function:

```SELECT LEVEL FROM DUAL
CONNECT BY LEVEL < 10
```

By using a recursive CTE

Recursive common table expressions are cool, yet utterly unreadable. the equivalent of the above Oracle CONNECT BY clause when written using a recursive CTE would look like this:

```WITH "1 to 10"(V) AS (
SELECT 1 FROM DUAL
UNION ALL
SELECT V + 1 FROM "1 to 10"
WHERE V < 10
)
SELECT * FROM "1 to 10"
```

By using Oracle’s MODEL clause

A decent “best of” comparison of how to do things in SQL wouldn’t be complete without at least one example using Oracle’s MODEL clause (see this awesome use-case for Oracle’s spreadsheet feature). Use this clause only to make your co workers really angry when maintaining your SQL code.

Bow before this beauty!

```SELECT V
FROM (
SELECT 1 V FROM DUAL
) T
MODEL DIMENSION BY (ROWNUM R)
MEASURES (V)
RULES ITERATE (10) (
V[ITERATION_NUMBER] = CV(R) + 1
)
ORDER BY 1
```

### Conclusion

There aren’t actually many nice solutions to do such a simple thing in SQL. Clearly, PostgreSQL’s `GENERATE_SERIES()` table function is the most beautiful solution. Oracle’s `CONNECT BY` clause comes close. For all other databases, some trickery has to be applied in one way or another.

Unfortunately.

### 10 responses to “How to Create a Range From 1 to 10 in SQL”

1. Eric S says :

Postgresql’s terminology “generate a series” is also more correct than your “create a range”. When I saw the title I thought you were going to talk about something like

CREATE DOMAIN one_to_ten AS INTEGER
CHECK(
VALUE > 0
AND VALUE <= 10
);

Nice article though! The comparative functionality articles are useful.

• lukaseder says :

Thanks for the nice feedback.

You’re right, hence the GENERATE_SERIES() function in PostgreSQL.

The SQL standard documents, on the other hand, do not reference the term “series”, but they reference the term “range” quite often, e.g. in the context of a “range variable”, in window functions’ frame clauses (ROWS | RANGE UNBOUNDED PRECEDING, etc.), and even as a type enhancement for procedural SQL, as in:

```type SMALLINT is range bs .. ts;
```
• Eric Schwarzenbach says :

Well, the range is the span which is more of a abstract definition, whereas the sequence is a concrete set of values across such a range. I suppose you are in effect defining a range in creating this sequence, but the goal seems to be more the sequence itself. But sorry to belabor such a trivial point. :^)

• lukaseder says :

;-) Let me counter this with the fact that a SQL sequence is something that can only be incremented statefully across all sessions. Titling this article “How to Create a Sequence From 1 to 10 in SQL” would make it that much less interesting ;-)

2. Plopes says :

“By creating enough self-joins of a sufficent number of values”

Self-joins do not need CTE, you can use-it like this:

```SELECT v0.n + v1.n
FROM (
SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
)v0,
(
SELECT 1 n UNION ALL SELECT 5 UNION ALL SELECT 9
)v1
WHERE v0.n + v1.n <= 10
ORDER BY 1
```
• lukaseder says :

You’re right. That’s what the article meant by:

Another “dumb”, yet a bit more generic solution would be to create only a certain amount of constant values in a table, view or CTE

… “in a table, view or CTE”. The CTE solution was used to show an easy way to re-use the basis for data generation.

3. Cordell duToit says :

SELECT TOP 10
ROW_NUMBER() OVER (ORDER BY c1.id) as ColName
FROM syscolumns AS c1
CROSS JOIN syscolumns AS c2

• lukaseder says :

True, that’s an additional option for SQL Server. I’d even replace that `ORDER BY c1.id` by `ORDER BY (SELECT 1)`