At 04:45 PM 28/06/2004 -0600, you wrote:

>I need to join two tables together and get the latest date in a column in
>the right table for every record in the left table
>
>My attempt was this sql:
>
>SELECT t.Name, t.CreationDateTime, Max(r.TimeStamp)
> FROM
> Test t left join Results r on t.TestProgramID = r.TestProgramID
> WHERE t.Archived = 1
> GROUP BY t.Name
>
>I get an SQL error -104, Invalid expresion in the Aggregate function or
>the Group By Statement
>
>If I take out the Max function and the Group by statement, I get a list of
>all Timestamps for each Left side record like I would expect.
>
>Why isn't the Max function working?

You have two problems here. The first is that you can't include
"lower-level" columns in the output: everything must be at the level of
the aggregation - the GROUP BY performs the aggregation at the level of
Name, whereas the "TimeStamp" column is at a level beneath the aggregation.

The other problem is that Timestamp is a reserved word. Presumably, if you
have a database column of this name, it is defined with double quotes. You
must always use the double quotes and case-sensitivity when referring to
such columns in SQL statements.

The left join seems irrelevant in this query. Aggregations ignore nulls.

>Is there a different way?

Assuming you want the output of the Max() expression to appear on multiple
rows and you don't actually want one row per Name, you could do a
correlated subquery to get that expression value

SELECT t.Name, t.CreationDateTime,

(SELECT Max(r."TimeStamp") from Results r
where r.TestProgramID = t.TestProgramID) as MaxTimestamp

FROM Test t
WHERE t.Archived = 1

I don't know whether this is what you're after, though. If you really want
the output grouped on Name, you'll need to omit CreationDateTime from your
original query.

/heLen