Well, in traditional SQL you'd use a sub-query for this type of operation.
In MySQL 4.1+ you could do this:
SELECT t1.myindex, t1.myval, t1.mycat
FROM mytest t1
WHERE myindex = (SELECT max(t2.myindex) FROM mytest t2 WHERE
t2.mycat=t1.mycat);
once again, just illustrating the theory, it may not work as is.
SQL is really *meant* to have sub-queries, so the temp table solution is
just a work around until sub-queries make it into the production version of
MySQL. At least, that's how I understand it anyway.
Chris
-----Original Message-----
From: Noah Spurrier [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 21, 2004 1:46 PM
To: Chris
Cc: [EMAIL PROTECTED]
Subject: Re: Confused by max and group by
This seems bizarre. Although I am the SQL neophyte and it is perhaps not
my right to whine about the mysteries of SQL, but this seem very surprising
and nonintuitive.
In general, it seems like there is no reason to select multiple fields if
one of the fields
uses the max() function because the other resulting fields are meaningless
(in that they are unrelated to the field returned by max()).
Your temp table solution makes sense. I'll use that.
Yours,
Noah
On Wednesday 21 April 2004 11:07 am, Chris wrote:
> You aren't making any mistakes, it's just not possible to do. You can't
rely
> on which row MySQL will return when using a GROUP BY clause.
>
> The standard method would be to do something like this:
>
> CREATE TEMPORARY TABLE mytemptable
> SELECT max(myindex) as myindex, mycat
> FROM `mytest`
> GROUP BY mycat;
>
> then
> SELECT myval, myotherrows, mycat
> FROM `mytemptable`
> LEFT JOIN mytest USING(myindex,mycat)
>
> note: I haven't tested the above code, it's just an example of the theory
>
> Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
