>>>

>>>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
>
>Rich,
>Makes sense. While you've sample data to test it would you test this for me:
>

>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 = m.agentcode AND dw2.ctrl_num = m.ctrl_num ;
>                        AND dw2.item_num=dw1.item_num);
>INTO CURSOR try1

TIA
>Cetin


That was actually my first try. The query ran for so long I eventually interrupted it (it was on 99% complete for well over 2 minutes). The results improved when I used the alias (rather than the memvar) in the sub-query. Here are the optimization levels for this query

Using index tag Agentcode to rushmore optimize table dw2
Using index tag Ctrl_num to rushmore optimize table dw2
Rushmore optimization level for table dw2: full
Rushmore optimization level for table d: none