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