Finding correct record using MAX(date)

I've run into this problem that I've struggled with for some time now, reading a lot of post but not being able to solve it by my self.

I've a table holding record of members status changes in a club. "Status_Members"

MemberID     Date       StatusID

23         2011-04-01      1
46         2011-05-01      2
23         2011-10-01      2
46         2011-06-01      1

This is joined to a table "Status_Type" holding the values of the StatusID.

StatusID     StatusName
1             Active
2             Passive

Joining the 2 together gives:

MemberID     Date       StatusName
23         2011-04-01      Active
46         2011-05-01      Passive
23         2011-10-01      Passive
46         2011-06-01      Active

Now I want to be able to find the status of the members on a specific date.

Let's say 2011-07-01.

So I tried with this code:

SELECT S.MemberID, MAX(Date), ST.StatusName FROM Status_Members S, Status_Type ST
WHERE ST.StatusId=S.StatusID
AND
Date <= '2011-07-01'
Group By MemberID
Order by Date DESC

So I expect the result to be:

MemberID, MAX(Date), StatusName

46       2011-06-01   Active
23       2011-04-01   Active

But I get this "wrong" result

MemberID, MAX(Date), StatusName

46       2011-06-01   Passive
23       2011-04-01   Active

Why am I getting the result Passive instead of Active on MemberID 46 ??

Can Any开发者_运维知识库one help me out here???

Best regard a Newbie

When you aggregate with MAX, you don't have control over the representative row that you're getting which isn't being aggregated or gouped by, namely your status ID. So make two queries and join them:

SELECT sm.MemberID AS MemberId, MaxDate, StatusName
FROM Status_Type AS st
JOIN Status_Members AS sm ON (st.StatusId = sm.StatusId)
JOIN (SELECT MemberID, MAX(Date) AS MaxDate FROM Status_Members WHERE Date <= xxx GROUP BY MemberID) AS sq
  ON (sm.MemberId = sq.MemberId AND sm.Date = sq.MaxDate);

The subquery makes an intermediate table MemberId, MaxDate, and we join that again the Member_Status table again to retrieve the status at that date.

In your query, you GROUP BY member.id. Your MAX() function thus returns the MAX-date for a given member, which for 46 is 2011-06-01. It should not return ST.StatusName as it is not part of an aggregate function or your GROUP BY, so I guess it just takes the first occurence.

Have you tried to GROUP also BY the statusName?

SELECT S.MemberID, MAX(Date) as maxDate, ST.StatusName FROM Status_Members S, Status_Type ST
WHERE ST.StatusId=S.StatusID
GROUP BY MemberID, StatusName
HAVING maxDate <= '2011-07-01'
ORDER By Date DESC