How to get the previous ID
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to get the previous ID

  1. #1
    Join Date
    Mar 2001
    Posts
    19
    Team,

    I want to get the previousID and the nextID for a particular message. Is there any function in oracle for the same??.

    My table read as follows:

    MessageID parentID message
    1 0 first
    2 0 second
    3 0 third
    4 1 reply first
    5 2 reply second
    6 3 reply third

    i want to get all the IDS that matches the messageID 1 and the replies to them as well, since there would be lot of replies i want the prevID of the message 0, if it have one.

    Since the replies are not ordered i cannot choose messageID = messageID-1 or messageID+1.

    Can anyone please please help me out
    Seenu


  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    use the start with and connect by prior id =parent id clause

  3. #3
    Join Date
    Mar 2001
    Posts
    19
    Hrishy, thank you for the answer can you please letme know with a small example of how to do this please??

    Expecting your reply
    Seenu

  4. #4
    Join Date
    Jan 2001
    Posts
    318
    If you are using Sequencing on the messageId can you do
    seqname.nextval, seqname.lastval...



    [Edited by sonaliak on 05-21-2001 at 11:32 AM]
    Sonali

  5. #5
    Join Date
    Jan 2001
    Posts
    318
    How about this..


    Select max(MessageID) from Message
    where MessageID <( Select max(MessageID) from message);

    and


    Select max(MessageID) + 1 from Message;
    Sonali

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    You *need* to have something that you can sort by. Most likely, that will be something like an Inserted_TS. Once you have that, the previous message would be the record with no parent that has the maximum inserted_ts less than the one you're on. You can do this via the type of query sonaliak did or using an analytical fundtion. But first, you need that field to sort on.

    - Chris

  7. #7
    Join Date
    Mar 2001
    Posts
    19
    Yes i can sort out the table with the input as the sectionID. (i.e.) all the messages that belong to a particular section

    viz.,

    select * from messages where sectionID=1 order by parentID desc;

    This will give me all the messages for a particular section and would be ordered by parentID.

    my output will be as follows:
    ---------------------------------

    messageID messge parentID sectionID title
    1 first 0 1 first title
    5 second 0 1 second title
    7 first re 1 1 firstreply
    9 second re 5 1 secondreply.
    10 second re re 9 1 second second reply.

    When i click the first reply to the second message (message second re and messageID 9) i need to get the 5 as my prev ID as this is the prevID for this message and 10 as my nextID as this is the next reply for this message. How can i do this one??

    can anyone please please help me out.

    Thanks all for your help,
    seenu

Posting Permissions

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



Click Here to Expand Forum to Full Width