In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Is it possible to generate a fibonacci sequence with a single SQL query?

Which database are you using, and do you need one number per row, or in a comma delimited list?

drop table a

create table a(a int, b int)

insert a values (1, 1)

insert a values (2, 1)

-- repeat this next statement as many times as you want...

insert a select max(a)+1, sum(b) from a where a>(select max(a) from a)-2

-- get your results with this statement

select * from a

A few syntax changes might be needed for oracle ("insert into a" instead of "insert a" - I do not have Oracle on my machine so I cannot test it right now)

I brushed up some VB(A) code in the archive. It don't use recursion as often seen (which is _extremely_ slow for anything more than a handful of elements); instead it fills a simple array (very fast):

Public Function FibonacciElementDec( _

ByVal lngElements As Long) _

As Variant

' Returns the value of element lngElements in the

' Fibonacci sequence of numbers.

' Max. number returned:

' 50095301248058391139327916

'

' 2004-10-03. Gustav Brock, Cactus Data, CPH.

Const clngIndexMin As Long = 0

Const clngIndexMax As Long = 139

Dim decValue As Variant

If lngElements < clngIndexMin Or lngElements > clngIndexMax Then

' Not a valid input. Return error.

decValue = CDec(-1)

Else

' Return last number in sequence in array.

decValue = FibonacciSequenceDec(lngEl

End If

FibonacciElementDec = decValue

End Function

Public Function FibonacciSequenceDec( _

ByVal lngElements As Long) _

As Variant()

' Build and return array with Fibonacci sequence of numbers.

' Count of elements is determined by lngElements.

' Max. number returned in array:

' 50095301248058391139327916

'

' 2004-10-03. Gustav Brock, Cactus Data, CPH.

' Min. index of sequence per definition.

Const clngIndexMin As Long = 0

' Max. possible index of sequence for datatype Decimal.

Const clngIndexMax As Long = 139

Dim adecSeq() As Variant

Dim lngIndex As Long

If lngElements < clngIndexMin Or lngElements > clngIndexMax Then

' Not a valid input.

Else

' Build and fill array with the Fibonacci sequence of numbers.

ReDim adecSeq(clngIndexMin To lngElements)

For lngIndex = clngIndexMin To lngElements

If lngIndex < 2 Then

' Values of the first two elements are 0 and 1 per definition.

adecSeq(lngIndex) = CDec(lngIndex)

Else

' Value is the sum of the two preceding numbers.

adecSeq(lngIndex) = CDec(adecSeq(lngIndex - 2)) + CDec(adecSeq(lngIndex - 1))

End If

'' Debug.Print adecSeq(lngIndex);

Next

End If

FibonacciSequenceDec = adecSeq()

End Function

You can create a simple loop - using either the array from the last function or just repetitive calls of the first function, speed is so fast that you hardly can tell the difference - to insert the index and the value in two fields of the table.

Note that the first element (index zero) is zero.

If you need more than 139 elements I have the equivalent functions for datatype Double but note that you will loose absolute accuracy for element index 76 and beyond.

/gustav

A nice weekend challenge though...

Select top 1000000 id = identity(int,1,1) Into Numbers from sysobjects s1, sysobjects s1, sysobjects s1, sysobjects s1

In general, an actual table as opposed to a derived table will perform better once you get above a few thousand rows because statistics are kept for a real table and derived tables will be converted to unindexed work tables at some point.

What exactly is your algorithm? We may be able to optimize it.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

A single statement using a derived table - but it's limited to the first 72 digits before rounding errors cause errors.

select cast (

floor((power((cast(1 as decimal(30,0))+sqrt(cast(5

power((cast(1 as decimal(30,0))-sqrt(cast(5

(power(cast(2 as decimal(30,0)),id)*sqrt(ca

as decimal(30,0)) fn

FROM

(

select top 72 cast((a0.id + a1.id) as decimal(30,0)) id FROM

(select 0 id union select 1 union select 2 union select 3 union select 4 union

select 5 union select 6 union select 7 union select 8 union select 9) a0,

(select 0 id union select 10 union select 20 union select 30 union select 40 union

select 50 union select 60 union select 70 union select 80 union select 90) a1

) Numbers