>Hi,
>
>I am writing a SQL Select with a group by. The SQL Select has JOIN and some fields from the Joined table are to be selected. I can include them into the SQL Select using the MAX() clause. For example,
>

>select Table1.Field1, max(table2.Field1) as FieldXyz, max(table2.Field2) as FieldAbc from Table1 
>join Table2 on Table1.pk_field = Table2.pk_field group by table1.Field1
>

>The above works. But I wonder if I should avoid using MAX() and come up with a different way of getting the fields from Table2?

As Naomi says, it depends what you're trying to do. If there's a one-to-one relationship between the tables, this should work. But if your join would produce multiple records in Table2 without the GROUP BY, then you may not be getting accurate results this way. To be specific, the values for Field1 and Field2 might come from different records in Table2.

Assuming this is simplified and that you actually are aggregating something from Table1, the alternative way to do this is to have a query of Table1 with the GROUP BY as a derived table in a larger query that does the join to Table2. Something like:

SELECT dt.Field1, dt.SomethingElse, Table2.Field1 AS FieldXYZ, Table2.Field2 ;
   FROM (SELECT Field1, SUM(OtherField) AS SomethingElse, MAX(pk_Field) AS pk_Field FROM Table1 GROUP BY Field1) AS dt ;
       JOIN Table2 ;
         ON dt.pk_Field = Table2.pk_Field

This code makes explicit the confusing thing about what you're doing. If you're aggregating on Table1.Field1, but joining on Table1.pk_Field (and assuming pk_Field means it's the primary key in that table) implies Field1 is unique for each record in Table1 so the aggregation isn't meaningful. Maybe Table2.pk_Field isn't actually a pk there, but is a foreign key?

There's a different possibility about what you mean to do. Maybe you're trying to find the child with a max value for some field and then pull the other fields from that record? It's easy in SQL Server (with the First_Value and Last_Value functions), but harder in VFP. If that's what you're looking for, say so.

Tamar