Prepare for the second stage of testing

Typical problems

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 num
UNION 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:

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
 ) z
WHERE 5*5*(a-1)+5*(b-1) + c <= 100
ORDER BY 1;

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 ) z WHERE 5*5*(a-1)+5*(b-1) + c <= 100 ORDER BY 1;

    Scheme

No scheme

The condition

WHERE 5*5*(a-1)+5*(b-1) + c <= 100

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
 ) z
WHERE 5*5*(a-1)+5*(b-1) + c <= 100
ORDER BY 1;

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 ) z WHERE 5*5*(a-1)+5*(b-1) + c <= 100 ORDER BY 1;

    Scheme

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.