From: | "John D(dot) Burger" <john(at)mitre(dot)org> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Aggregates |
Date: | 2007-06-21 14:29:38 |
Message-ID: | 0446DCA4-C9AC-40EE-BB46-559F109F8197@mitre.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Jun 21, 2007, at 09:22, Richard Huxton wrote:
> Naz Gassiep wrote:
>> Hi,
>> If I have a table with users and a table with messages, is it
>> possible to have a query that returns user.* as well as one extra
>> column
>> with the number of messages they have posted and the data and time of
>> the last message? At the moment I am using a subquery to do this,
>> however it seems suboptimal. Is there a better way?
>
> Not really. You have three separate queries really:
> 1. User details
> 2. Total number of messages posted
> 3. Details on last message posted
>
> Unless you have a messaging-summary table that you keep up-to-date
> with triggers you're looking at a three-part query.
Certainly except for the user details it could be a single GROUP BY
with several aggregate functions, something like:
select user.userid, count(*), max(message.datetime)
from user join message using (userid)
group by user.userid;
But if userid is UNIQUE, then so is user.*. You can't do something
like GROUP BY USER.*, but you can group by all the user columns
you're actually interested in selecting:
select userid, user.name, user.address, count(*), max
(message.datetime)
from user join message using (userid)
group by userid, user.name, user.address;
As to whether this is faster or prettier than a subquery, I dunno.
- John D. Burger
MITRE
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2007-06-21 14:45:57 | Re: Excell |
Previous Message | Reid Thompson | 2007-06-21 14:27:14 | PostgreSQL question re select for update (locking??) |