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]
