List:General Discussion« Previous MessageNext Message »
From:gerald_clark Date:February 21 2006 8:06pm
Subject:Re: JOINs with result of aggregate function fails with error #1054
View as plain text  
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.
Thread
JOINs with result of aggregate function fails with error #1054Guillaume Boissiere21 Feb
  • Re: JOINs with result of aggregate function fails with error #1054gerald_clark21 Feb
  • Re: JOINs with result of aggregate function fails with error #1054SGreen21 Feb