Skip site navigation (1) Skip section navigation (2)

Re: Query combination query. (fwd)

From: john-paul delaney <jp(at)justatest(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Query combination query. (fwd)
Date: 2005-02-22 14:54:47
Message-ID: Pine.LNX.4.44.0502221530330.13607-100000@angelico.justatest.com (view raw or flat)
Thread:
Lists: pgsql-novice
> 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.





Responses

pgsql-novice by date

Next:From: Bruno Wolff IIIDate: 2005-02-22 15:01:28
Subject: Re: Query combination query. (fwd)
Previous:From: Bruno Wolff IIIDate: 2005-02-22 14:37:24
Subject: Re: Perl DBI connection to Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group