John D. Burger wrote:
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;
Ah, but this just includes the time of the last message, not its data.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
