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