Table name INFORMATION Item Eff_date Status A 02/15/05 IN A 01/15/03 AC A 01/15/99 AC B 02/15/05 AC B 01/15/03 IN C 02/15/07 AC C 02/15/05 AC C 01/15/03 IN Criteria: Get Item that has maximum Eff_date that is <= 03/15/05 and Status = 'AC'. So basically, the output should look like Item Eff_date Status B 02/15/05 AC C 02/15/05 AC I have SELECT Item, Eff_date, Status FROM INFORMATION A WHERE eff_date = (SELECT max(B.Eff_date) FROM INFORMATION B WHERE A.Item = B.Item AND Status = 'AC'AND Eff_date <= 03/15/05) But I am getting the information for Item 'A' also which should not be in the output. Any Idea guys???