List:General Discussion« Previous MessageNext Message »
From:Guillaume Boissiere Date:February 21 2006 7:23pm
Subject:JOINs with result of aggregate function fails with error #1054
View as plain text  
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

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