> SELECT subject,created,topic_id,(select count(topic_seq) from
> ap_form_msq as b where b.topic_id=a.topic_id) from ap_forum_msq as a
> where topic_seq=1;
>
> > How to combine the following 2 queries into 1?
> > (1) SELECT subject, created, topic_id FROM ap_forum_msg WHERE topic_seq
> > = 1;
> > (2) SELECT topic_id, count(topic_seq) FROM ap_forum_msg GROUP BY
> > topic_id;
> > (Table ap_forum_msg)
> >
> > Column | Type | Modifiers
> > ----------+--------------------------+---------------
> > topic_id | integer | not null
> > topic_seq | integer | not null
> > author_id | integer | not null
> > created | timestamp with time zone | default now()
> > subject | character varying(100) |
> > msg_text | character varying |
> > forum_id | integer | not null
Hello List...
Given Sean's answer above, I then managed the simple part to get the author
name from another table, but was stumped when I thought it
would be better to return the created date of the last message (having the same
topic_id) rather than the first one. The last message can be found in
either of two ways:
(1) the latest 'created' for a topic_id or
(2) the highest 'topic_seq' number for a topic id.
I've failed miserably in my attempts - any enlightenment greatly appreciated.
Many thanks,
/j-p.