>>>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