how to select data with recursive query

Question

1.00/5 (1 vote)

See more:

i have this simple table with 2 columns "number" and "previous number"defined with this values:
number | previous number
281 | 0
280 | 281
279 | 280
278 | 279
277 | 278
276 | 277
275 | 276
274 | 275
273 | 274
272 | 273

and the following select

select 
    tmp.op_nr,
    @op_nr := tmp.previous_op_nr,
    @depth:=@depth+1
from 
     (select @op_nr=280, @depth:=0 ) as vars,
    dwh.temporary_table tmp
   
where tmp.op_nr=@op_nr and 
    @depth<10;

and for this query it should return

VB

281 | 0
280 | 281
279 | 280

if i change the depth to 2 and @op_nr to 275
it should return something like

VB

277 | 278
276 | 277
275 | 276

but i am missing something in the select statement and doesn't work like this.
what modification should i make?

Posted 12-Mar-13 6:21am

Gabriel Sas

Add a Solution

Comments

Richard C Bishop 12-Mar-13 12:23pm

What results are you getting if not the desired results?

Gabriel Sas 12-Mar-13 12:24pm

276 | 277
277 | 278
278 | 279

in this order

Richard C Bishop 12-Mar-13 12:31pm

Just do an order by and add desc to the end of what you are ordering by.

2 solutions

Solution 1

You may want to look into Recursive Queries Using Common Table Expressions[^]. From what I gathered from that page CTEs are exactly what you are looking for. There is an elaborate example at the bottom of that page that looks to me as if it would solve your problem.

[Modified]
I realised a bit late that you are talking about MySql, so I had a look and turned up this: Managing Hierarchical Data in MySQL[^]. You may want to skip down to the part about "The Nested Set Model" as this seems to be a better fit to your problem than the "The Adjacency List Model".
[/Modified]

Keep us posted of your findings! :)

Regards,

— Manfred

Posted 12-Mar-13 6:28am

Manfred Rudolf Bihy

Updated 12-Mar-13 6:34am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gabriel Sas · Accepted Answer · 2013-03-12T06:28:00

Solution 2

found the problem, it's a spelling one, i have put ":" here:
changed this

SQL

(select @op_nr=280, @depth:=0 ) as vars,

to

SQL

(select @op_nr:=280, @depth:=0 ) as vars,

Posted 12-Mar-13 6:28am

Gabriel Sas