03:47

# The number-sequence generation page 1

Sometimes it is necessary to take number-sequence in query. It may be end in itself or subproduct for getting, say, date sequence. Let for example it is necessary to take sequence of integers from 1 to 100 with step 1. Of course, it is possible to construct frontally such generation, i.e.

`SELECT 1 AS numUNION ALL SELECT 2...UNION ALL SELECT 100`

And if does it take 1000 numbers? Besides expenses of time for the typing of such quantity of operators, we shall inefficiently use the network traffic, passing the long queries to a server for executing.

Cartesian product (CROSS JOIN) is able to help, which is used rarely unless as a subproduct. Important property of Cartesian product is strength of result (number of rows), which equals product of strengths of tables that participant in Cartesian product. I.e. if we need to take generation of 100 numbers we can use Cartesian product of tables, either of them contains 10 records. So,

`SELECT * FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 ) x CROSS JOIN (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 ) y;`

The result presented here is two-column table with 100 rows. In so doing, each value from first subquery (numbers from 1 to 10) combines with each value from second (similar) subquery:

1 1
1 2
...
1 10
2 1
2 2
...
2 10
...

Now it only remains to calculate values themselves. Given solution, say, number in first column will be represented as (tens -1), and second columns represents units. So, we'll write in our query instead of SELECT * FROM … the following query:

`SELECT 10*(a-1)+b FROM …`

It gives necessary result.

And why don't take 3 tables (subquery)? The more size of generated order the more tables must take to get shorter query. Reason by analogy and based on 5*5*5 = 125, we obtain:

Console
Execute
`SELECT 5*5*(a-1)+5*(b-1) + c AS num FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 ) x CROSS JOIN (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 ) y CROSS JOIN (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 ) zWHERE 5*5*(a-1)+5*(b-1) + c <= 100ORDER BY 1;`

The condition

`WHERE 5*5*(a-1)+5*(b-1) + c <= 100`
is used to restrict sequence by value 100, but not 125.

Examine “working” model. Let there is a need to take 100 consequent-unoccupied numbers of models on basis of Product table. The idea consists to find maximal number of model and so 100 values with step 1 using sequence generation.

Console
Execute
`SELECT (SELECT MAX(model)  FROM Product ) + 5*5*(a-1)+5*(b-1) + c AS num FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 ) x CROSS JOIN (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 ) y CROSS JOIN (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 ) zWHERE 5*5*(a-1)+5*(b-1) + c <= 100ORDER BY 1;`

I will not cite the result set of this query to economize kilobytes. Test it yourself having clicked the "Execute" button.
If your server supports CTE, generation of numerical sequence becomes essentially simpler. You can use the Console of this tutorial to solve the problem, considered here, in such manner. You can address to examples of the chapter on recursive CTE where a few numerical sequences are discussed.

Suggested exercises: 66, 94, 104, 118, 143

 Pages 1 2