MySQL ODBC Connector - JOINs with result of aggregate function fails with error #1054

Author

JOINs with result of aggregate function fails with error #1054

Guillaume Boissiere

2006-02-25, 9:43 am

------ =_Part_2738_23046761
.1140549809192
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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=3DMyISAM;

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=3D'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=3Dmaxid
WHERE l.is_visible=3D'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

------ =_Part_2738_23046761
.1140549809192--

gerald_clark

2006-02-25, 9:43 am

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/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2006-02-25, 9:43 am

--=_alternative 006E40E88525711C_=
Content-Type: text/plain; charset="US-ASCII"

"Guillaume Boissiere" <gboissiere@gmail.com> 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

--=_alternative 006E40E88525711C_=--

Sponsored Links