I use 2 different Mysql to check my query and I found this strange result. This happend after I upgrade mysql to version 4.1.20 I wonder if my setup are wrong? Can anyone point me direction how should I solve this. thanks a bunch... ;D below is the detail CREATE TABLE `portfolio` ( `record_id` int(11) NOT NULL auto_increment, `company_id` int(11) NOT NULL default '0', `FY` int(4) NOT NULL default '0', `started_mon` int(11) NOT NULL default '0', `field_name` enum('equity','owned_share') NOT NULL default 'equity', `field_value` float NOT NULL default '0', PRIMARY KEY (`record_id`), UNIQUE KEY `field_name` (`field_name`,`company_id`,`FY`,`started_mon`) ); INSERT INTO `portfolio` VALUES (1, 4, 2006, 1, 'equity', 558000); INSERT INTO `portfolio` VALUES (2, 4, 2006, 1, 'owned_share', 0.67); INSERT INTO `portfolio` VALUES (3, 12, 2005, 1, 'owned_share', 0.88); INSERT INTO `portfolio` VALUES (4, 10, 2005, 1, 'owned_share', 0.5); INSERT INTO `portfolio` VALUES (5, 6, 2006, 1, 'equity', 6.191e+06); INSERT INTO `portfolio` VALUES (6, 14, 2005, 1, 'owned_share', 0.67); INSERT INTO `portfolio` VALUES (7, 6, 2006, 1, 'owned_share', 0.53); INSERT INTO `portfolio` VALUES (8, 5, 2005, 1, 'owned_share', 1); INSERT INTO `portfolio` VALUES (9, 8, 2006, 1, 'owned_share', 0.5); INSERT INTO `portfolio` VALUES (10, 4, 2005, 1, 'equity', 550000); INSERT INTO `portfolio` VALUES (11, 4, 2005, 1, 'owned_share', 0.45); INSERT INTO `portfolio` VALUES (12, 12, 2006, 1, 'equity', 2.067e+06); INSERT INTO `portfolio` VALUES (13, 12, 2005, 1, 'equity', 2e+06); INSERT INTO `portfolio` VALUES (14, 7, 2005, 1, 'equity', 192000); INSERT INTO `portfolio` VALUES (15, 7, 2006, 1, 'owned_share', 1); INSERT INTO `portfolio` VALUES (16, 7, 2005, 1, 'owned_share', 0.55); INSERT INTO `portfolio` VALUES (17, 6, 2005, 1, 'equity', 6e+06); INSERT INTO `portfolio` VALUES (18, 6, 2005, 1, 'owned_share', 1); INSERT INTO `portfolio` VALUES (19, 1, 2000, 1, 'equity', 0); INSERT INTO `portfolio` VALUES (20, 1, 2000, 1, 'owned_share', 1); INSERT INTO `portfolio` VALUES (21, 9, 2006, 1, 'equity', 1.887e+06); INSERT INTO `portfolio` VALUES (22, 9, 2005, 1, 'equity', 1.8e+06); INSERT INTO `portfolio` VALUES (23, 9, 2006, 1, 'owned_share', 0.5); INSERT INTO `portfolio` VALUES (24, 9, 2005, 1, 'owned_share', 1); INSERT INTO `portfolio` VALUES (25, 11, 2006, 1, 'equity', 1.3161e+07); INSERT INTO `portfolio` VALUES (26, 11, 2005, 1, 'equity', 1.3e+07); INSERT INTO `portfolio` VALUES (27, 11, 2006, 1, 'owned_share', 0.495); INSERT INTO `portfolio` VALUES (28, 11, 2005, 1, 'owned_share', 0.49); INSERT INTO `portfolio` VALUES (29, 14, 2004, 1, 'equity', 125000); INSERT INTO `portfolio` VALUES (30, 8, 2005, 1, 'equity', 3.283e+06); INSERT INTO `portfolio` VALUES (31, 8, 2005, 1, 'owned_share', 1); INSERT INTO `portfolio` VALUES (32, 13, 2006, 1, 'equity', 6.41806e+07); INSERT INTO `portfolio` VALUES (33, 13, 2005, 1, 'equity', 5.34342e+07); INSERT INTO `portfolio` VALUES (34, 13, 2000, 1, 'owned_share', 1); INSERT INTO `portfolio` VALUES (35, 10, 2006, 1, 'equity', 6.746e+06); INSERT INTO `portfolio` VALUES (36, 10, 2005, 1, 'equity', 6e+06); INSERT INTO `portfolio` VALUES (37, 14, 2006, 1, 'owned_share', 0.8); INSERT INTO `portfolio` VALUES (38, 7, 2007, 1, 'owned_share', 1); When I try this query in MySQL 4.1.20-log (this is redhat machine) SELECT FY, company_id, field_value FROM portfolio WHERE field_name = 'owned_share' AND ( FY, company_id ) IN ( SELECT max( FY ) , company_id FROM portfolio WHERE field_name = 'owned_share' AND FY <=2006 AND company_id IN ( 4, 5, 6, 8, 12, 13, 14, 7, 9, 10, 11 ) GROUP BY company_id ) I got this result FY company_id field_value 2005 6 1 2006 7 1 2006 8 0.5 2006 9 0.5 2005 11 0.49 2006 14 0.8 But when I try the same query in MySQL 4.1.11-standard-log (windows machine , this is the desired result) FY company_id field_value 2006 4 0.67 2005 5 1 2006 6 0.53 2006 7 1 2006 8 0.5 2006 9 0.5 2005 10 0.5 2006 11 0.495 2005 12 0.88 2000 13 1 2006 14 0.8