SQL query - left outer join

[caff]

Hi,

I have a left outer join query that links two tables:

Table 1.
field_a char(10) <- primary key
field_b char(20)

Table 2.
field_c char(10) <- links to field_a in table 1
field_d datetime

There may be several records in table 2 for every record in table 1. field_c is not unique in table 2.

I want to join a left outer join from table 1 to table 2, but I only want to return the most recent value in table 2, i.e. the most recent datetime in field_d.

Any ideas on how to do this in a SQL query?

 

[caff]

I'm not too familiar with the group by clause, but it's saying:

Column name 'field_d' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Trouble is I don't want to group it by field_d - just but group_c as you suggested

Any ideas?
thanks

 

[caff]

Yes

 

[caff]

Cheers guys - that's worked a treat.

My SQL trigger is now stupidly complex

(on the bad side - tricky to debug later. on the good side - job security )