Forum OpenACS Q&A: Response to SQL: Order by and Group by
4: Response to SQL: Order by and Group by (response to 1)
Posted by Bob OConnor on 11/17/00 12:08 PM
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.