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???

SELECT Item, Eff_date, Status
FROM INFORMATION A
WHERE
eff_date = max(Eff_date) AND Status = 'AC' AND eff_date <= 03/15/05);

I havent tested it but i think the code should be something like this.....