[thelist] mysql: max in joins?

[rudy]

> Does anyone know off hand if you can have a max()
> as part of a join query in mysql?

yes -- you can not, before version 4.1

your query was

  select meta_articles.id
       , meta_articles.subject
       , meta_articles.created
       , meta_comments.max(created) as updated
       , meta_comments.name as who
    from meta_comments
left join meta_articles
      on meta_comments.articles_id = meta_articles.id
   where meta_articles.cats_id = 1
   group by id
   order by created ASC;

my guess is, you want every article in category 1, whether or not
it has comments, but if so, only the most recent

if that's what you want, that query won't get it  ;o)

it would be the other way round, articles left join comments

except that it requires a subquery, which requires 4.1

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

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

not sure i explained that terribly well

rudy