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]
