>>Hi
>>
>>Can I, in 1 SELECT statement, achieve the following:
>>
>>Table1 contains fields PK = Primary Key, FK = Foreign Key, Date = Date.
>>
>>I need to obtain the records from the table that have the highest date entry, do I need to select the dates first and then include those results in a sub-query in a 2nd Select?
>>
>>Any ideas?
>>
>>Thanks
>>Kev
>
>Kevin,
>No you don't have to do 2 SQLs. max() returns just one record so you could use that as a scalar value in 'where'. ie:
>
>where ddate = (select max(...) ...)
>
>A sample that demonstrates this (from FoxyClasses tips) :
>

>create cursor myGrouper (Ino i, ddate d, irec i)
>for ix = 1 to 20
>	insert into myGrouper ;
>		values (int(rand()*5), date()-int(rand()*100), ix)
>endfor
>select * from myGrouper order by 1,2
>
>* This one is the tip *
>select * from myGrouper A where ddate = ;
>(select max(ddate) from myGrouper B where a.ino = b.ino) ;
>order by ino
>

>First a cursor created. Ino represents the FK and irec represents PK. Records are inserted where ddate is random (dates for a particular ino are out of order).
>First SQL (select * from myGrouper order by 1,2) is there just to show how would be the dates if listed in order.
>Cetin

That's a simple case. If you would have several max date records, you would have several records.

But what if you need to find only one (or few) records with maximum date for each particular group, say, by Town?