loading..
Русский    English
03:29

Equivalent of recursive CTEs

Let’s consider a simple query generating a Fibonacci sequence (in a Fibonacci sequence, every number is the sum of the two preceding ones, and the first two numbers are 1 and 1):

  1. SELECT IF(X=1, Fn_1, Fn_2) F
  2. FROM(
  3.   SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2
  4.   FROM
  5.     (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a,
  6.     (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b,
  7.     (SELECT @I := 1, @J := 1)IJ
  8. )T,
  9.   /*A fake table ensuring single-column output of the sequence*/
  10.   (SELECT 1 X UNION ALL SELECT 2)X;

This query generates 18 Fibonacci numbers (excluding the first two):

F
2
3
5
8
13
21
34
55
89
144
233
377
610
987
1597
2584
4181
6765

Now, let’s analyze how it works.

Lines 5) and 6) generate 9 rows. So far, there is nothing extraordinary here.

In line 7), two variables @I, @J are declared and assigned the value 1.

The third line does the following: initially, the variable @I is assigned the sum of values of two variables. Then, the same expression is assigned to the variable @J – but with the changed value of @I.

In other words, values in the SELECT statement are calculated from left to right – also see the note at the beginning of this article.

Take into account that the variables are assigned new values for each of our 9 data records, that is, when processing of a new row begins @I and @J contain values calculated for the preceding row.

To fulfill this task in other DBMSs, we’d have to resort to a recursive query!

Notes:

Variables have to be declared in a separate subquery (see line 7); if a variable was declared directly within the SELECT statement, it most likely would be evaluated only once (although the actual behavior depends on the server version). The type of the variable is stipulated by the value it is initialized with. If the variable is assigned a NULL value, its type will be BLOB.

As mentioned before, the order the records are processed in by the SELECT statement depends on custom sorting. Here is a simple illustration of how custom row numbering is done:

  1. SELECT val, @I:=@I+1 Num
  2. FROM
  3.   (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a,
  4.   (SELECT @I := 0)I
  5. ORDER BY val;

Val Num
10 1
20 2
30 3
50 4

Bookmark and Share
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100