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]