Most databases do not have a native way of generating a series of numbers for ad-hoc use; however, common table expressions can be used with recursion to emulate that type of function.
The following example generates a common table expression called
Numbers with a column
i which has a row for numbers 1-5:
--Give a table name `Numbers" and a column `i` to hold the numbers WITH Numbers(i) AS ( --Starting number/index SELECT 1 --Top-level UNION ALL operator required for recursion UNION ALL --Iteration expression: SELECT i + 1 --Table expression we first declared used as source for recursion FROM Numbers --Clause to define the end of the recursion WHERE i < 5 ) --Use the generated table expression like a regular table SELECT i FROM Numbers;
This method can be used with any number interval, as well as other types of data.