ojup

Thank you. ;D That works beautifully. But still the problem of this version persist. I will try to upgrade to 4.1.22 version, maybe the bug is gone. Still this makes me afraid to upgrade. Anybody has version 5.1 and try this? In my case it looks like mysql does not like a function like MAX or DISTINCT or other in the subquery inside IN() if we use more than 1 column to compare inside IN() function. Just my opinion. Thanks again

Thats what exactly the question that makes me confuse. Both server return the exact same result for the inner query. max( FY) company_id 2006   4 2005   5 2006   6 2006   7 2006   8 2006   9 2005   10 2006   11 2005   12 2000   13 2006   14 Does anyone has version 4.1.20 that have the same problem?

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