[thelist] mysql: max in joins?

[Emma Jane Hogbin]

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