>>>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.
I will be using a datetime for the changedate so it shouldn't matter, but I concede the point.
Thanks!