How to get the previous ID

[seenu27]

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

[hrishy]

use the start with and connect by prior id =parent id clause

[seenu27]

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

[sonaliak]

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]

[sonaliak]

How about this..


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

and


Select max(MessageID) + 1 from Message;

[chrisrlong]

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

[seenu27]

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