I know that Distinct will give you just one record per contact but I'm not 100% certain if its the first or the last of the results set. It might be worth giving it a try with your data and check the results.
HTH
Caroline


>I have two tables, say contact and note. I want to write a single query that works for SQL Server (and ideally for VFP with minimum changes). I want to return a single record for each contact, and the most recent note for that contact (if a note exists).
>
>I would pseudo code this as:
>
>

>SELECT * FROM contact LEFT JOIN (SELECT TOP 1 * FROM note ORDER BY date DESC WHERE contact.id = note.cid) ON contact.id = note.cid
>WHERE other_conditions
>

>
>OR
>
>

>SELECT *, MAX(note.date) FROM contact LEFT JOIN note ON contact.id = note.cid GROUP BY contact.id
>

>
>The second sort of works in VFP with engine behaviour in 7.0 mode. I cannot find any feasible method in SQL Server.