> 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.





---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, Feb 22, 2005 at 15:54:47 +0100,
john-paul delaney <jp@justatest.com> wrote:
>
> 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.

The standard way to do this is to use the max function and select the
row that matches this value.

However the nonstandard DISTINCT ON construct is a better way to go if
you don't need to be portable.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, 22 Feb 2005, Bruno Wolff III wrote:

> The standard way to do this is to use the max function and select the
> row that matches this value.
>
> However the nonstandard DISTINCT ON construct is a better way to go if
> you don't need to be portable.
>
Thanks Bruno... I'd like to combine this all into one single query, if
that's possible.

/j-p.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org