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