>>>>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 )
Maybe it must be:
SELECT Mb.*, Sh.* ;
FROM Members mb ;
INNER JOIN StatusHistory sh ON Mb.MemberId = sh.MemberId ;
WHERE CTOBIN(RIGHT(MAX(DTOS(sh.ChangeDate)+BINTOC(sh.StatusId )),4)) IN ;
(SELECT CTOBIN(RIGHT(MAX(DTOS(ChangeDate)+BINTOC(StatusId )),4)) ;
FROM StatusHistory GROUP BY MemberId )
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.