Hi Naomi Thanks very much for your reply.
I can see that this works in VFP9, but in VFP8(Which my app is in) I get a syntax error
Can this be made to work in VFP8..I am not sure what the difference is in this statement VFP8 vs VFP9
Regards,
Gerard
>>I Have Following Table:
>>
>>Customer StockGroup Price PriceDate
>>C01 G01 100.00 1 Jan 2014
>>C01 G01 200.00 1 Dec 2014
>>C01 G01 300.00 1 Jun 2014
>>C02 G55 400.00 1 Nov 2014
>>C02 G55 500.00 1 Jun 2014
>>C02 G55 600.00 1 Feb 2014
>>
>>I want to do a Select Cutomer,StockGroup,Latest(Price) Group by Customer,StockGroup
>>
>>Result would be:
>>C01 G01 200.00 (200.00 being dated 1 Dec 214 whcih is the latest Price Date in the Group C01 - G01)
>>C02 G55 400.00 (400.00 being dated 1 Nov2014 whcih is the latest Price Date in the Group C02 - G55)
>>
>>i.e. Instead of say Max(Price) , I want the Most Recent Price to appear on the Destination Table
>>
>>Is there any Sql Select that will do this ?
>>
>>Tia
>>Gerard
>
>You can do it this way
>
>select SI.* from (select Customer, StockGroup, max(dateCol) as LastDate from StockInfo GROUP BY Customer, StockGroup) LastDate INNER JOIN StockInfo SI
>ON SI.Customer = LastDate.Customer and SI.StockGroup = LastDate.StockGroup and SI.DateCol = LastDate.LastDate
>
>If you have several rows with max date, it will select all of them using this approach.