SQL Select in Table with two IDs

[Kossio]

Can those who have the know-how help me with this problem?

So it's a table with Id and Previous_Id (don't know why, it's just the way the developers have made it..)
Now, I need a Select that starts from Id, checks for possible Previous_Id and jumps to that Id which has no Previous_Id (Null).
I mean, if Id has Previous_Id (i.e. it's not Null), then the Previous_Id is read as the main Id and then again you look to find out if there's any Previous_Id facing it - if yes, you do the same until you get to Null. It's that last Id which I need along with the first Id where it all started.
But how can this be done with Select?

 

[r937]

the answer is: recursively

and there's no way to do that in ANSI SQL

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[simian336]

Perhaps a data sample and an expected result sample would be helpful.

Simi

 

[Trevoke]

In Ruby, you'd do something like that - but when it comes to handling the SQL, it's pseudocode, since I don't know anything about the way you're set up.

table = (select id, previous_id from mytable)
# table = [ [1, nil], [2, 1], [3, nil], [4, nil], [5, 2], ...]
id_and_original_id = []
# That array will hold our results

table.each do |id, prev_id|
  # start recursion if there's a previous_id
  original_id = prev_id ? get_original_id prev_id : nil
  # Store the result
  id_and_original_id << [id, original_id]
end

def get_original_id oldid
# Find the row where the oldid is the original id
  previous_set = (select id, previous_id from mytable where id = oldid)
  return previous_set[0] if previous_set[1].nil?
  get_original_id previous_set[1]
end

puts id_and_original_id

Completely untested. Wrap your mind around it before running this, just in case it triggers the Singularity.

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?