>>>>Hi All:
>>>>
>>>>Consider
>>>>
>>>>
>>>>Item# Date Price
>>>>1111 2/3/04 111.22
>>>>2222 2/3/05 111.22
>>>>3333 2/3/06 111.22
>>>>4444 2/3/07 111.22
>>>>1111 2/3/08 222.22
>>>>
>>>>Select Item, max(date), Price ;
>>>>from foo ;
>>>>group by item ;
>>>>into cursor boo
>>>>
>>>>
>>>>I want the latest price, so this would be the result:
>>>>
>>>>
>>>>Item# Date Price
>>>>2222 2/3/05 111.22
>>>>3333 2/3/06 111.22
>>>>4444 2/3/07 111.22
>>>>1111 2/3/08 222.22
>>>>
>>>>
>>>>Problem is that VFP doesn't like this and generates an error GROUP BY clause missing. How do I solve this?
>>>>
>>>>Thanks,
>>>>
>>>>Yossi
>>>
>>>
>>>
>>>Select Foo.Item, Foo.date, Foo.Price ;
>>>from foo ;
>>>INNER JOIN (SELECT Item, MAX(Date) AS Date;
>>> FROM Foo;
>>> GROUP BY Item) Foo1;
>>> ON Foo.Item = Foo1.Item AND Foo.Date = Foo1.Date;
>>>into cursor boo
>>>
>>
>>Boris:
>>
>>Thanks.
>>
>>However, could you modify the SQL to account for the possibility of two records with the same date. I still only want 1.
>>
>>example:
>>
>>
>>Item# Date Price Shoe Size
>>1111 2/3/04 111.22 1
>>2222 2/3/05 111.22 2
>>3333 2/3/06 111.22 9
>>4444 2/3/07 111.22 8
>>1111 2/3/08 222.22 3
>>1111 2/3/08 222.22 7
>>>>
>>
>>Note that I also need the shoe size, but I don't care which one I get.
>>
>>So the result would be:
>>
>>
>>Item# Date Price Shoe Size
>>2222 2/3/05 111.22 2
>>3333 2/3/06 111.22 9
>>4444 2/3/07 111.22 8
>>1111 2/3/08 222.22 7 or 3, I don't care
>>
>
>What if the prices are different?
>What do you want?
>
>Select Foo.Item, Foo.date, MAX(Foo.Price) AS Foo.Price ;
>from foo ;
>INNER JOIN (SELECT Item, MAX(Date) AS Date;
> FROM Foo;
> GROUP BY Item) Foo1;
> ON Foo.Item = Foo1.Item AND Foo.Date = Foo1.Date;
>GROUP BY Foo.Item, Foo.date;
>into cursor boo
>
Now: How about when the data is in 2 files, FooA and FooB and the latest date could be in either. Could you do this in one SQL statement with UNION? How?
