I have following working query:
select children.autor as child, parents.autor as parent, count(*) from comments children
left join comments parents on (children.parentid = parents.commentid)
group by child, parent
order by count(*) desc
limit 4;
which produces following output:
child | parent | count
peter | max | 154
alex | peter | 122
peter | kARL | 82
stephen | alex | 50
Now the comments table also has a column 'bodytext' that is the actual comment and I want to include the last comment in the selection for each pair of child and parent.
So in the first line I want the last comment that peter wrote in reply to max. So far I don't even know how to approach this. A subquery? Some sort of window function?
If I use (max)bodytext, it almost does exactly what I want.. just the longest comment which is not really want I want.
id
field? If so, you might just select the comment body with the maxid
out of all the comments thatpeter
wrotemax
. I'll give it a shot later if you're still having issues as I'm a bit short on time at the moment.