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


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Ðåéòèíã@Mail.ru Rambler's Top100 Alt Óïðàæíåíèÿ ïî SQL: îáó÷åíèå, òåñòèðîâàíèå, ñåðòèôèêàöèÿ ïî ÿçûêó SQL Copyright c 2002-2006. All rights reserved.