Hi Tamar,

Thanks...your comments helpful.

Albert

>>Hi Y'all,
>>
>>I am just updating an app from VFP7 to 9 and knew I would have to deal with the change to the "GROUP BY" part of SQL. I have quite a few spots where I grouped by a single field or two before but now this needs updating. Some SELECT statements have a lot of fields so I don't relish adding all the new fields to the GROUP BY list.
>>
>>How have some of you handled this?
>>
>>a) just add them all and quit complaining :-)
>>b) write a function to dig out all of the table names in a string and macro expand it (with the original "group by" field first)
>>
>>I realize that the new syntax is indeed better - I used to have to sometimes sort things in a particular order to get the desired result - because the old way would take the value of a non-grouped field from the *last* record read it seemed.
>
>First, remove all the extraneous fields from the query; that'll improve performance as well as help solve the problem.
>
>Second, check whether the fields that are ungrouped and unaggregated are the same in every record in the group or not. If they're the same, it's easy to add them to the GROUP BY or wrap them in MAX() or MIN().
>
>If they're not the same, you need to adjust the GROUP BY criteria or otherwise modify the query so that you get the right result. Most likely, what you really need is a subquery or derived table to first get the right data. Here's an example:
>
>

>SELECT Orders.Order_ID, Customer.Company_Name as Cust_Name, ;
>       Shippers.Company_Name AS Ship_Name, Orders.Order_Date ;
>  FROM Orders ;
>    JOIN Customer ;
>      ON Orders.Customer_ID = Customer.Customer_ID ;
>    JOIN Shippers ;
>      ON Orders.Shipper_ID = shippers.Shipper_ID ;
>  WHERE Orders.Order_Date = ;
>    (SELECT MAX(Order_Date) ;
>       FROM Orders Ord WHERE Orders.Customer_ID=Ord.Customer_ID );
>  ORDER BY Cust_Name ;
>  INTO CURSOR MostRecentOrders
>

>
>Hope this helps.
>
>Tamar