[thelist] mysql: max in joins?
rudy
r937 at interlog.com
Tue Mar 4 17:16:01 CST 2003
> 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
More information about the thelist
mailing list