Latest Date Entry

[Guest]

I have a table of information that I extracted out of our company database
and need to retrieve the latest date for a Purchase Order.

My table consist of CMI#, PO#, Effective Date & Price.

Sample data:
CMI# PO# Eff Date Current PO $
03992 59884 3/3/05 1.28
03992 59884 12/13/04 1.18
03992 59884 10/7/04 1.11
03992 59884 8/27/04 1.04
03992 59884 7/23/04 0.97
03992 59884 4/28/03 0.83
03992 59884 5/1/01 0.80
03992 59884 5/12/00 0.91
03992 59884 12/28/98 0.83

But I just want line:
03992 59884 3/3/05 1.28

I need this for each CMI#, also I can't have it showing any future Purchase
Orders either.

Any help would be great, I seem to have a brain stall on this.

Thanks, Stacey

 

[Guest]

Try something along the lines of
SELECT * FROM MyTable T1
WHERE [Effective Date] IN
(SELECT TOP 1 [Effective Date] FROM MyTable T2 WHERE T2.PO# = T1.PO#)

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

That worked with some tweeking!
Thanks!

Try something along the lines of
SELECT * FROM MyTable T1
WHERE [Effective Date] IN
(SELECT TOP 1 [Effective Date] FROM MyTable T2 WHERE T2.PO# = T1.PO#)

Hope This Helps
Gerald Stanley MCSD

I have a table of information that I extracted out of our company database
and need to retrieve the latest date for a Purchase Order.

My table consist of CMI#, PO#, Effective Date & Price.

Sample data:
CMI# PO# Eff Date Current PO $
03992 59884 3/3/05 1.28
03992 59884 12/13/04 1.18
03992 59884 10/7/04 1.11
03992 59884 8/27/04 1.04
03992 59884 7/23/04 0.97
03992 59884 4/28/03 0.83
03992 59884 5/1/01 0.80
03992 59884 5/12/00 0.91
03992 59884 12/28/98 0.83

But I just want line:
03992 59884 3/3/05 1.28

I need this for each CMI#, also I can't have it showing any future Purchase
Orders either.

Any help would be great, I seem to have a brain stall on this.

Thanks, Stacey