Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: MySQL Algorithms
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
281 | 0
280 | 281
279 | 280
if i change the depth to 2 and @op_nr to 275
it should return something like
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 7:21am
Comments
richcb at 12-Mar-13 12:23pm
   
What results are you getting if not the desired results?
Gabriel Sas at 12-Mar-13 12:24pm
   
276 | 277
277 | 278
278 | 279
 
in this order
richcb at 12-Mar-13 12:31pm
   
Just do an order by and add desc to the end of what you are ordering by.
Rate this: bad
good
Please Sign up or sign in to vote.

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! Smile | :)
 
Regards,
— Manfred
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

found the problem, it's a spelling one, i have put ":" here:
changed this
(select @op_nr=280, @depth:=0 ) as vars,
to
(select @op_nr:=280, @depth:=0 ) as vars,
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 386
1 Marcin Kozub 225
2 Sergey Alexandrovich Kryukov 215
3 /\jmot 189
4 Praneet Nadkar 173
0 OriginalGriff 8,289
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,624
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 12 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100