>>>>>>>Just compare the speed. I am very curious which way is better. Never had enough records to make a proper tests.
>>>>>>
>>>>>>Just FYI
>>>>>>
>>>>>>Using live data (local hard drive - not on the server), running both queries 1,000 times resulted in 57.307 seconds for yours and 74.672 for Sergey's.
>>>>>
>>>>>Mike, what about my as well?
>>>>>
>>>>>How many tests did you run?
>>>>
>>>>Which was your query? This one seems like it won't work because sh.statusID cannot match CTOBIN(RIGHT(MAX(DTOS(ChangeDate)+BINTOC(StatusId )),4)?
>>>>
>>>>SELECT Mb.*, Sh.* ;
>>>> FROM Members mb ;
>>>> INNER JOIN StatusHistory sh ON Mb.MemberId = sh.MemberId ;
>>>> WHERE sh.StatusId IN ;
>>>> (SELECT CTOBIN(RIGHT(MAX(DTOS(ChangeDate)+BINTOC(StatusId )),4)) ;
>>>> FROM StatusHistory GROUP BY MemberId )
>>>
>>>Yes, this one. Try it. I'm working on something, read the tek-tips thread for an explanation why it works.
>>
>>statusid is a guid. CTOBIN(RIGHT(MAX(DTOS(ChangeDate)+BINTOC(StatusId )),4)) cannot match.
>>
>>Here's the query with my field names.
>>
>>SELECT Mb.*, Sh.* ;
>> FROM Members mb ;
>> INNER JOIN StatusHistory sh ON Mb.uId = sh.uId ;
>> WHERE sh.sth_pk IN ;
>> (SELECT CTOBIN(RIGHT(MAX(DTOS(effDate)+BINTOC(sth_pk)),4)) ;
>> FROM StatusHistory GROUP BY uId ) ;
>> into cursor testing
>>
>>I get the error function argument value type or count is invalid.
>
>With GUID you need to modify this method a bit. How many chars is your GUID? Remove CTOBIN/BINTOC then and use simple

RIGHT(MAX(DTOS(effDate) + sth_pk),16)

if your GUID 16 chars.

I see what's going on here. Neat trick! The max causes it to pick the highest date and the pk is added on. Taking the right most part of the string results in the PK.

This technique is a little quicker too. By the way, who showed you this technique?
MisterX's 52.324
Borislav's 57.408
and Sergey's 77.366