One to Many only want the Greatest date from the Many 1

[MFritts253]

I have two tables that join with a Contract table with PK_Contract_ID to another table with Contract Detail with FK_Contract_ID which has a one to many. What i want from the detail table is a date field. Everytime a customer makes a payment the detail table gets a new record of the payment with a new date. What i want is to grab just the most recent payment for that contract ID.

This is what i am getting right now
C_ID ITEM DATE
001 1000 11/2
001 1000 11/3
001 1000 11/10
001 1000 12/5

What i want --- the contract id with the greatest date
C_ID ITEM DATE
001 1000 12/5

Cheers,
Michael

 

[ddiamond]

The following query should return the detail record with the greatest date for any given contract_id.

select a.c_id, a.item, a.date
from Contract_Detail a
where not exists (
  select *
  from Contract_Detail b
  where a.c_id = b.c_id 
    and b.date > a.date);

 

[MFritts253]

AAAAAAh i figured it out i'll just use the MAX function,
I am brain dead today