Forum OpenACS Q&A: Response to SQL: Order by and Group by

Posted by Bob OConnor on

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.