Is there a generic way to generate an arbitrary linear sequence in SQL?

Question

Is there a SQL query I can do that will generate a linear sequence like

1, 2, 3, 4, 5, 6, 7 ... x+1

or

2, 7, 12, 17, 22 ... 2+5x

(where each number is an entry in a row of the resulting table)

Answer 1

No. (Unless precreating a table of numbers counts as a generic way.)

In SQL Server this can be done with a recursive CTE or generating a sequence using ROW_NUMBER()

Answer 2

Use sequence

Answer 3

SQL Server and Oracle now implement the ANSI standard ROW_NUMBER() windowing function, but you'd need a table to work off of:

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS __ROW, ID, Name
FROM SomethingWithANameAndAnID
ORDER BY __ROW;

Or you could use a recursive Common Table Expression in SQL Server (not sure if Oracle implements this yet):

WITH cte AS
(
    SELECT 1 AS num
    UNION ALL
    SELECT (num + 1) AS num FROM cte
    WHERE num < @SomeMaximum
)
SELECT * FROM cte;

Answer 4

In Oracle you can do:

select ROWNUM linear_sequence from dual CONNECT BY LEVEL <= x;

where x is the end of the sequence.

Answer 5

You can specify an increment when creating a sequence :

CREATE SEQUENCE mysequence  INCREMENT BY 5 START WITH 2;