>

>select * from myTable t1 ;
> where myDate = (select max(myDate) from myTable t2 where t1.Name == t2.Name)
>

Cetin
>


I'm still down at VFP 6 so the following comments may not apply to you.

I've had to do something like this, used the same logic, and found that the queries ran very slowly for a large (850K records) table.

SELECT dw1.* FROM dwell dw1;
 WHERE dw1.agentcode = m.agentcode AND dw1.ctrl_num = m.ctrl_num;
   AND dw1.dwedid = (SELECT MAX(dwedid) FROM dwell dw2; 
                      WHERE dw2.agentcode=dw1.agentcode AND dw2.ctrl_num=dw1.ctrl_num ;
                        AND dw2.item_num=dw1.item_num); 
INTO CURSOR try1

Rushmore optimization level for table dw2: none
Rushmore optimization level for table e: none
Using index tag Agentcode to rushmore optimize table dw1
Using index tag Ctrl_num to rushmore optimize table dw1
Rushmore optimization level for table dw1: full
Rushmore optimization level for intermediate result: none

The total time for the query was 52 seconds. The DWELL table has index tags on AgentCode, Ctrl_Num, Item_num, and DwEdid.

By breaking it into two queries

SELECT item_num,MAX(dwedid) AS dwedid FROM dwell;
 WHERE agentcode = m.agentcode AND ctrl_num = m.ctrl_num AND dwedid <= m.edid;
 GROUP BY item_num;
 INTO CURSOR curxxx

SELECT dw1.* FROM dwell dw1, curxxx;
 WHERE dw1.agentcode = m.agentcode AND dw1.ctrl_num = m.ctrl_num;
   AND dw1.dwedid = curxxx.dwedid and dw1.item_num=curxxx.item_num; 
INTO CURSOR try2

Using index tag Agentcode to rushmore optimize table dwell
Using index tag Ctrl_num to rushmore optimize table dwell
Using index tag Dwedid to rushmore optimize table dwell
Rushmore optimization level for table dwell: full
Using index tag Agentcode to rushmore optimize table dw1
Using index tag Ctrl_num to rushmore optimize table dw1
Rushmore optimization level for table dw1: full
Rushmore optimization level for intermediate result: none

Query time was reduced to approximately half a second.

As I mentioned, this is for VFP 6 and it may not be applicable in later versions