loading..
Русский    English
23:10

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.

  1. SELECT 1 AS num
  2. UNION ALL
  3. SELECT 2
  4. ...
  5. UNION ALL
  6. 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,

  1. SELECT *
  2. FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
  3. UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
  4. UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
  5. ) x CROSS JOIN
  6. (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
  7. UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
  8. UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
  9. ) 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:

  1. SELECT 10*(a-1)+b
  2. 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
  1. SELECT 5*5*(a-1)+5*(b-1) + c AS num
  2. FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
  3. UNION ALL SELECT 4 UNION ALL SELECT 5
  4. ) x CROSS JOIN
  5. (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
  6. UNION ALL SELECT 4 UNION ALL SELECT 5
  7. ) y CROSS JOIN
  8. (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3
  9. UNION ALL SELECT 4 UNION ALL SELECT 5
  10. ) z
  11. WHERE 5*5*(a-1)+5*(b-1) + c <= 100
  12. ORDER BY 1;

The condition

  1. 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
  1. SELECT (SELECT MAX(model)
  2. FROM Product
  3. ) + 5*5*(a-1)+5*(b-1) + c AS num
  4. FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
  5. UNION ALL SELECT 4 UNION ALL SELECT 5
  6. ) x CROSS JOIN
  7. (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
  8. UNION ALL SELECT 4 UNION ALL SELECT 5
  9. ) y CROSS JOIN
  10. (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3
  11. UNION ALL SELECT 4 UNION ALL SELECT 5
  12. ) z
  13. WHERE 5*5*(a-1)+5*(b-1) + c <= 100
  14. ORDER 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
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
today
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.