If there are multiple rows in the table2 per each table1 PK, which particular row are you interested in? Do you want to get 1 row with the maximum values in one of the columns of the table2? Or you don't care which values and from which rows you're going to get per PK? In the second case your approach is fine. In the first case it's a bit of a tricky problem for VFP and easy for SQL Server using either cross apply or ROW_NUMBER() function.

>First, thank you and Naomi for your messages.
>
>Second, I want this SQL Select to work with both, VFP and SQL Server.
>
>Third, my goal is to select unique values of the Table1.Field1 and for each unique value to get a couple of fields from the Parent table. I am not aggregating anything from the Table1.
>My main concern with the MAX() is that - I don't know - it may result in inaccurate values from Table2.
>
>After I posted the message, I thought, what if I do Distinct(Table1.Field1) as Field1, will it make the code more reliable?
>
>Which way is better - not speed wise but accuracy - Group By or Distinct()?
>
>Thank you.
>
>>>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