>Converting VFP6 SQL to VFP9 SQL.
>
>Assuming that there is a table called custname with three columns: custnum, address,ordernum. There is no unique key. When the folloing SQL was run in VFP6, the address associated with the max(ordernum) was returned. The address was not chosen at random. How do you code this SQL in VFP9?
>
>

>select custnum, address, max(ordernum) from cust group by custnum
>

>

Brenda,

You're mistaken that address for max(ordernum) was returned. The address was returned for the last physical record in the group which in your case happens to be record with max(ordernum). If for some reason pysical order of records didn't correspond to the ordernum you would get incorrect result. The following query with derived table should give you correct result.

select cust.custnum, cust.address, cust.ordernum from cust ;
	JOIN ( select custnum, max(ordernum) AS maxord from cust group by custnum) dt1 ;
		ON cust.custnum = dt1.custnum AND cust.ordernum  = dt1.maxord