"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
