Main page
The number-sequence generation.
(the theme was proposed by Shurgenz)
S. Moiseenko
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
UNION ALL SELECT 2
...
UNION ALL SELECT 100
And if does it take 1000 numbers? And yet restriction on size of query may exist.
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 participant in Cartesian
product of tables. I.e. if we need to take generation of 100 numbers we can use
Cartesian product of tables, either of them contain in 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
Here result presented two-column table with 100 rows. At that each value from first
subquery (numbers from 1 to 10) combines with each value from second (similar)
subquery:
a
b
1
1
1
2
...
1
10
2
1
2
2
...
2
10
...
Now it only remained to calculate value. Given solution, say, will be number in
first column represented tens -1 and second represented unities. So, write in
our query instead of SELECT * FROM …
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 find shorter query. Reason by analogy and based on 5*5*5 =
125 we get:
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
Condition WHERE 5*5*(a-1 )+ 5*(b-1) + c <=100 is used to restrict sequence by
value 100 and 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 1using sequence generation.
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
I will not cite the result set of this query to economize kilobytes. Examine
yourself.
» Given examples here can be done directly on the website by selecting the check
box “Without checking” on the page with
SELECT exercises .
Main page