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