Thank you Todd and Phil. "Use the source, Bob". So I did, and found part of my solution in the q-and-a-new-answers.tcl file.
They use a view of the bboard table and join it to bboard:
View "bboard_new_answers_helper"
Attribute | Type | Modifier
--------------+-----------+----------
root_msg_id | char(6) |
topic_id | integer |
posting_time | timestamp |
View definition: SELECT b.root_msg_id, b.topic_id,
b.posting_time FROM bboard b WHERE (b.refers_to NOTNULL);
And the select:
select bnah.root_msg_id,count(*) as n_new,max(bnah.posting_time)
as max_posting_time, to_char(max(bnah.posting_time),'YYYY-MM-DD')
as max_posting_date, bboard.one_line as subject_line,
bboard.user_id as poster_id
from bboard_new_answers_helper as bnah, bboard
where sysdate()::date - bnah.posting_time::date < 7
and bnah.root_msg_id = bboard.msg_id
and bnah.topic_id >0
group by bnah.root_msg_id, bboard.one_line, bboard.user_id
order by max_posting_time desc;
This does most of what I need. It seems to allow other fields
in the select that are not part of the group by. I'll make a few modifications and do more testing so that I understand it.
-Bob
