JOINs with result of aggregate function fails with error #1054
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
Re: JOINs with result of aggregate function fails with error #1054
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOINs with result of aggregate function fails with error #1054
Guillaume Boissiere [EMAIL PROTECTED] wrote on 02/21/2006 02:23:29 PM: 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 you will have to use two queries. You might have been able to use a subquery but your version doesn't support them. The reason for the error is that the MAX() function isn't evaluated until you get to the GROUP BY portion of the query. This is AFTER all tables are JOIN-ed and all of the WHERE restrictions have been applied. The column name `maxid` won't exist until the MAX() value has been calculated so ... you have nothing to join to. Thus the error. sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine