I forgot to say I'm using FB 1.0

This seems to work...

select * from contacthistory A where
A.contactdate in (select max(contactdate) from contacthistory b where
a.contacttypeid = b.contacttypeid)

Can any one spot any problems?

> -----Original Message-----
> From: DaveR [mailto:mailings@...]
> Sent: 19 March 2002 00:46
> To: ib-support@yahoogroups.com
> Subject: [ib-support] How do I formulate this...
>
> I can't get my head round this one...
> I have a table define as
> CREATE TABLE CONTACTHISTORY
> (
> CONTACTID D_ID , integer not null
> CONTACTTYPEID D_ID ,
> CONTACTDATE D_DATE , Date not null
> USERID D_ID ,
> NOTES D_NOTES , VarChar(80)
>
> constraint PK_CONTACTHISTORY PRimary Key (ContactId, ContactType,
> ContactDate),
> constraint FK_CH_CONTACT Foreign Key (ContactId) references
> Contacts(Id),
> constraint FK_CH_CONTACTTYPE Foreign Key (ContactTypeId) references
> ContactTypes(Id),
> constraint FK_CH_USER Foreign Key (UserId) references Users(Id)
> )
>
> and I want to select the most recent record of each contacttype.
>
> One way to do it would be to write a procedure using a cursor through
> the CONTACTTYPES table doing a select on the CONTACTHISTORY table for
> each record on the CONTACTTYPES table.
>
> Is there a way this can be done in single query? I think there is,

but

> cannot quite formulate it.
>
> Any pointers would be appreciated.
>
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.338 / Virus Database: 189 - Release Date: 14/03/2002
>
>
>
> ------------------------ Yahoo! Groups Sponsor
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to

http://docs.yahoo.com/info/terms/

>
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.338 / Virus Database: 189 - Release Date: 14/03/2002
>

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.338 / Virus Database: 189 - Release Date: 14/03/2002