12:21

# 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):

`SELECT IF(X=1, Fn_1, Fn_2) FFROM(  SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2  FROM    (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a,    (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b,    (SELECT @I := 1, @J := 1)IJ)T,  /*A fake table ensuring single-column output of the sequence*/  (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:

`SELECT val, @I:=@I+1 NumFROM  (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a,  (SELECT @I := 0)IORDER BY val;`

Val Num
10 1
20 2
30 3
50 4