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
-----Original Message-----
From: Noah Spurrier [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 21, 2004 10:35 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Confused by max and group by
I'm having trouble with "max()" and "group by".
It seems pretty simple. I hope someone can point out my mistake.
I want to select the max index of a group.
In other words, I want to find the last record added for each group.
The problem I'm having is that the columns of the resulting rows
are mixed with different records. I get the expected indexes
returned, but the fields appear to be from another record and
not the fields associated with the index.
I ran this query:
SELECT max(myindex), myval, mycat
FROM `mytest`
GROUP BY mycat;
and I get the following results:
+--------------+-------+-------+
| max(myindex) | myval | mycat |
+--------------+-------+-------+
| 3 | one | A |
| 9 | one | B |
+--------------+-------+-------+
But I was expecting this:
+--------------+-------+-------+
| max(myindex) | myval | mycat |
+--------------+-------+-------+
| 3 | one | A |
| 9 | three | B |
+--------------+-------+-------+
This is my test data.
CREATE TABLE `mytest`(
`myindex` int(11) NOT NULL default '0',
`myval` varchar(40) NOT NULL default '',
`mycat` varchar(40) NOT NULL default '',
PRIMARY KEY (`myindex`)
) TYPE=MyISAM;
INSERT INTO `mytest` VALUES (1, 'one', 'A');
INSERT INTO `mytest` VALUES (2, 'two', 'A');
INSERT INTO `mytest` VALUES (3, 'three', 'A');
INSERT INTO `mytest` VALUES (4, 'one', 'B');
INSERT INTO `mytest` VALUES (5, 'two', 'B');
INSERT INTO `mytest` VALUES (6, 'three', 'B');
INSERT INTO `mytest` VALUES (7, 'one', 'B');
INSERT INTO `mytest` VALUES (8, 'two', 'B');
INSERT INTO `mytest` VALUES (9, 'three', 'B');
Yours,
Noah
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
