SQL query: Generating Consecutive Numeric Values (Oracle, DB2, SQL Server, PostgreSQL)

SQL studing: Generating Consecutive Numeric Values


You would like to have a "row source generator” available to you in your queries. Row source generators are useful for queries that require pivoting. For example, you want to return a result set such as the following, up to any number of rows that you specify:


If your RDBMS provides built-in functions for returning rows dynamically, you do not need to create a pivot table in advance with a fixed number of rows. That’s why a dynamic row generator can be so handy. Otherwise, you must use a traditional pivot table with a fixed number of rows (that may not always be enough) to generate rows when needed.


This solution shows how to return 10 rows of increasing numbers starting from 1. You can easily adapt the solution to return any number of rows.

The ability to return increasing values from 1 opens the door to many other solutions. For example, you can generate numbers to add to dates in order to generate sequences of days. You can also use such numbers to parse through strings.

DB2 and SQL Server

Use the recursive WITH clause to generate a sequence of rows with incrementing values. Using a recursive CTE will in fact work with the majority of RDBMS’s today. Use a one-row table such as T1 to kick off the row generation; the WITH clause does the rest:

	 1 with x (id)
	 2 as (
	 3 select 1
	 4   from t1
	 5  union all
	 6 select id+1
	 7   from x
	 8  where id+1 <= 10
	 9 )
	10 select * from x

 Following is a second, alternative solution for DB2 only. Its advantage is that it does not require table T1:

	1 with x (id)
	2 as (
	3 values (1)
	4  union all
	5 select id+1
	6   from x
	7  where id+1 <= 10
	8 )
	9 select * from x


In Oracle Database you can generate rows using the MODEL clause:

	1 select array id
	2   from dual
	3  model
	4    dimension by (0 idx)
	5    measures(1 array)
	6    rules iterate (10) (
	7      array[iteration_number] = iteration_number+1
	8    )


Use the very handy function GENERATE_SERIES, which is designed for the express purpose of generating rows:

	1 select id
	2   from generate_series (1, 10) x(id)



DB2 and SQL Server

The recursive WITH clause increments ID (which starts at 1) until the WHERE clause is satisfied. To kick things off you must generate one row having the value 1. You can do this by selecting 1 from a one-row table or, in the case of DB2, by using the VALUES clause to create a one-row result set.


In the MODEL clause solution, there is an explicit ITERATE command that allows you to generate multiple rows. Without the ITERATE clause, only one row will be returned, since DUAL has only one row. For example:

	select array id
	  from dual
	  dimension by (0 idx)
	  measures(1 array)
	  rules ()


The MODEL clause not only allows you array access to rows, it allows you to easily “create” or return rows that are not in the table you are selecting against. In this solution, IDX is the array index (location of a specific value in the array) and ARRAY (aliased ID) is the “array” of rows. The first row defaults to 1 and can be referenced with ARRAY[0]. Oracle provides the function ITERATION_NUMBER so you can track the number of times you’ve iterated. The solution iterates 10 times, causing ITERATION_NUMBER to go from 0 to 9. Adding 1 to each of those values yields the results 1 through 10.

It may be easier to visualize what’s happening with the model clause if you execute the following query:

	select 'array['||idx||'] = '||array as output
	  from dual
	   dimension by (0 idx)
	   measures(1 array)
	   rules iterate (10) (
	     array[iteration_number] = iteration_number+1

	array[0] = 1
	array[1] = 2
	array[2] = 3
	array[3] = 4
	array[4] = 5
	array[5] = 6
	array[6] = 7
	array[7] = 8
	array[8] = 9
	array[9] = 10


All the work is done by the function GENERATE_SERIES. The function accepts three parameters, all numeric values. The first parameter is the start value, the second parameter is the ending value, and the third parameter is an optional “step” value (how much each value is incremented by). If you do not pass a third parameter, the increment defaults to 1.

The GENERATE_SERIES function is flexible enough so that you do not have to hardcode parameters. For example, if you wanted to return five rows starting from value 10 and ending with value 30, incrementing by 5 such that the result set is the following:


you can be creative and do something like this:

	select id
	  from generate_series(
	         (select min(deptno) from emp),
	         (select max(deptno) from emp),
	       ) x(id)

 Notice here that the actual values passed to GENERATE_SERIES are not known when the query is written. Instead, they are generated by subqueries when the main query executes.

Вас заинтересует / Intresting for you:

A Brief History of SQL
A Brief History of SQL 5425 views Александров Попков Wed, 17 Oct 2018, 15:04:29
SQL queries: Filling in Missin...
SQL queries: Filling in Missin... 1308 views Денис Wed, 14 Jul 2021, 11:48:24
How to Retrieving a Subset of ...
How to Retrieving a Subset of ... 1708 views Денис Wed, 14 Jul 2021, 04:45:37
SQL: how to determine which ro...
SQL: how to determine which ro... 1405 views Денис Tue, 06 Jul 2021, 18:47:53
Comments (0)
There are no comments posted here yet
Leave your comments
Posting as Guest
Suggested Locations