>>>>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.
If it's not broken, fix it until it is.
My Blog