Postgres Select subgroup

Question

Wondering if there are any tricks in PG 9.3 that will help me?

TableID, CommentID, Comment, Orderid 
 1, 1,  "Jenn, Looks Good", 1
 2, 1,  "Benn, The Doc looks good.",    2
 3, 2,  "Like it",  1
 4, 3,  "Splling",  1
 5, 3,  "Spelling", 2
 6, 3,  "Check the Spelling", 3

I've got this table that is comments and rather than updating comments an edit is put in as the next "orderid" for the commentID. I'm looking for a select that will return this:

Benn, The Doc looks good.
Like it
Check the Spelling

I'm looking for something better looping through each of the commentID if it's possible.

My current solution is to Group by CommentID and then foreach select comment where commentid = ? order by orderid desc limit 1 into a temp and then selecting the temp table.

I was looking at this https://stackoverflow.com/a/12653000/3562373 since I have a parent key that has more info about the comment and came up with:

Select comment  
from commentInfo  
inner join ( Select * From  comment order by orderid  desc limit 1 ) on CommentInfo.CommentID = Comment.CommentID 
where commentinf = (The Group of comments I'm interested in)

But since the limit applies to whole table it isn't helping me

People who down vote should be forced to leave a reason why. I would love to know how to ask a propor question. — Ron H, May 05 '15 at 15:37

score 1 · Accepted Answer · answered May 01 '15 at 23:12

If what you want to do is to return the latest version for every CommentID (that is the one with highest OrderID) then you can find the latest version in a derived table and join with that:

select t.* from table t
join (
    select CommentID, max(orderid) orderid
    from table group by CommentID
) b on b.CommentID = t.CommentID and b.orderid = t.Orderid

Postgres Select subgroup

1 Answers1