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

    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
    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 ...


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

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

    Since you want to do this in SQL, I'd follow Chuck's CONNECT BY suggestion.
    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