Re: Confused by max and group by

[Brent Baisley]

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]