Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: Select in Table with two IDs

    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?

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    I'd create a PL/SQL function which grabs the most recent ID, where you pass in the root ID. So your main SQL would grab anything where PREVIOUS_ID is null, and pass the main ID into this procedure. Basically, go in the reverse order from what you described.

    Let PL/SQL walk up the chain... or... you might be able to slim down the code within that function, using CONNECT BY and LEVEL in your embedded SQL ...

    --=cf

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    google recursive SQL

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Oracle doesn't have recursive CTE's (yet).

    Since you want to do this in SQL, I'd follow Chuck's CONNECT BY suggestion.
    Code:
    SELECT id, level
    FROM whatever
    START WITH id = 99
    CONNECT BY PRIOR id = Previous_Id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •