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.
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,
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:
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:
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:
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.
I will not cite the result set of this query to economize kilobytes. Test it yourself having clicked the "Execute" button.