Guillaume Boissiere wrote:
>This must have been asked before but I could not find the answer searching
>the list archives.
>I have a simple table:
>CREATE TABLE `license` (
> `id` int(11) NOT NULL auto_increment,
> `firstname` varchar(100) NOT NULL default '',
> `lastname` varchar(100) NOT NULL default '',
> `host_address` varchar(100) NOT NULL default '',
> `is_visible` tinyint(4) NOT NULL default '1',
> PRIMARY KEY (`id`)
>) TYPE=MyISAM;
>
>
>This first query works (MySQL 4.0.25):
>
>SELECT l.id, MAX(l.id) AS maxid, l.host_address
>FROM `license` l
>WHERE l.is_visible='1'
>GROUP BY l.host_address
>ORDER BY maxid DESC
>
>but this second query fails with the error: #1054 - Unknown column 'maxid'
>in 'on clause'
>
>SELECT l.id, MAX(l.id) AS maxid, l.host_address, l2.firstname, l2.lastname
>FROM `license` l
>INNER JOIN license l2 ON l2.id=maxid
>WHERE l.is_visible='1'
>GROUP BY l.host_address
>ORDER BY maxid DESC
>
>Is there a way to do a join with the result of an aggregate function (in
>this case MAX(id))
>in one query, or do I have to use multiple queries for this?
>
>Thanks in advance!
>
>Guillaume
>
>
>
You cannot join on an aggregate function. The value of maxid cannot be
determined until after the join is completed.
