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