RE: Confused by max and group by
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]
Re: Confused by max and group by
I think what is happening is that you are getting the max value for one field, but the first values for the other fields. Try ordering you group by: SELECT max(myindex), myval, mycat FROM `mytest` GROUP BY mycat DESC; On Apr 21, 2004, at 1:35 PM, Noah Spurrier wrote: 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] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused by max and group by
The problem you are running into is that you are getting the max of one field and grouping by another. But then you want to get a third field that changes within the grouping. Perhaps this might work SELECT myindex, myval, mycat FROM `mytest` GROUP BY mycat ORDER BY myindex DESC; On Apr 21, 2004, at 4:47 PM, Noah Spurrier wrote: Unfortuantely, that didn't do it. I tried both DESC and ASC. I got the same incorrect result. This is too bad. It SEEMS like it should work... -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Confused by max and group by
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]
