>>>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.
But as in all status history tables, we know that the status may change during the day (asleep, booting, 1st coffee, eating, siesta, nth coffee, drinking, crawling home), so there's no place for a date field - we need a timestamp or a datetime.