Home » SQL & PL/SQL » SQL & PL/SQL » Latest row within sub-group (9i)
Latest row within sub-group [message #400773] Wed, 29 April 2009 08:13 Go to next message
nibeck
Messages: 3
Registered: March 2009
Junior Member
OK, this shouldn't be too hard, but lack of caffeine is tripping me up.

We have a table that has a recursive relationship with itself to store parent/child relationships. These are comments, and when the user edits a comment, we create a child that refers to the parent.

COMMENTS:
PK - (unique across table)
Group_ID - Groups comments into chunks (will only query for single group ID at a time)
Comment_Text
Create_Dt
Parent_ID (null for first comment, has value for edits)

Sample Data:

1, 100, First Comment, 1/1/2009, NULL
2, 100, Second COmment, 1/2/2009, NULL
3, 100, Edit of second comment, 1/3/2009, 2
4, 100, Second Edit of comment, 1/4/2009, 2

Requirement, return results that contain all Parents with no children and the MOST RECENT child for any edited comment.

So, in sample data, the desired output is:
1 - First Comment
4 - Second Edit of comment

This really reeks of PARTITION OVER, but I just can;t wrap my head around it.

Thanks,

_mike
Re: Latest row within sub-group [message #400779 is a reply to message #400773] Wed, 29 April 2009 08:29 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
How can we see a comment is parent of another one?
How can we see a comment is a child of another and which one?
How can we see a comment is an edition of another one and which one?

Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel

Previous Topic: how to write a procedure .......
Next Topic: cannot drop a user that is currently connected
Goto Forum:
  


Current Time: Tue Feb 11 17:05:30 CST 2025