>>>>
>>>>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
Thanks for the info.
Cetin
