>>I ran the two first selects and I see the problem.
>>
>>In the StatusHistory table we should have a PK, say, StatusID.
>>
>>The code should be
>>
>>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 )
>>
>>That's what happens when you write code based on a sample :)
>>
>>You're a cheater by not creating PK in StatusHistory :)
>
>OK, one second. Is there anything better about this approach than Sergey's?
There is a difference in these two approaches if you have several same days. In my case it still returns just one record, in Sergey's it would return all of them.
It depends on what do you want to achieve, do you want just one Max record doesn't matter which or multiple.
If it's not broken, fix it until it is.
My Blog