Latest row within sub-group [message #400773] |
Wed, 29 April 2009 08:13  |
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  |
 |
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
|
|
|