[thelist] mysql: max in joins?
Emma Jane Hogbin
emmajane at xtrinsic.com
Tue Mar 4 23:01:01 CST 2003
On Tue, Mar 04, 2003 at 06:16:26PM -0500, rudy wrote:
> my guess is, you want every article in category 1, whether or not
> it has comments, but if so, only the most recent
Yes. (I'd been playing around with the query and pasted the one with the
incorrect join.)
> 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field
> http://www.mysql.com/doc/en/example-Maximum-column-group-row.html
There appears to be a work-around in here but I'm so early on in the
process that I'm just going to modify the table to include the
last_comment_id and the join on that.
> you can, for each article, get aggregates like number of comments,
> date of last comment, and so on -- just not any of the non-aggregate
> columns, like who wrote the last comment
Which means that I can know what the max(created) comment is (i.e. newest)
but I have no (easy) way of connecting that date with the actual contents
of the rest of the row (in meta_comments). i.e. I can find max(created)
which is aggregate (calculated from all values in the column) but I cannot
get the rest of the values for that calculated row (non-aggregate) while
I'm doing the join.
> not sure i explained that terribly well
You gave me some keywords like "can't do" and "aggregate." Hopefully my
interpretation is right. Thanks for your help, rudy. :)
emma
PS While reading 3.5.4 I rediscovered the INSERT into SELECT statement.
I'd forgotten about it. What a great statement. :)
--
Emma Jane Hogbin
[[ 416 417 2868 ][ www.xtrinsic.com ]]
More information about the thelist
mailing list