Max Query issue getting more than 1 record

[psycoperl]

I am trying to use the following query to get the most recent comment record for a student. I am expecting either 0 or 1 records, however when I am running it I am sometimes getting more than one.

SELECT Max(CommentDate) AS MaxDate,
ReceivedOnATBEligible, NotATBeligible
FROM tblComments
GROUP BY StudentID, ReceivedOnATBEligible, NotATBeligible
HAVING (((StudentID)="000000000'));


Can anyone please advise?

Thanks

tblComments:
ATBCommentsID StudentID CommentDate ReturnedToSender Comment ReceivedOnATBEligible NotATBeligible
1 000000000 6/30/2011 1:50:55 PM True Test Record False True
2 000000000 6/30/2011 2:41:14 PM False True False
4 000000000 6/30/2011 2:41:41 PM False False True

 

[Norie]

It's because you are including other fields where the values are different for each record.

It appears that the values for fields ReceivedOnATBEligible and NotATBeligible are different.

 

[jasmith4]

There's more to it, including some trickery. When you group by some fields and then aggregate others, you'll get the aggregations for each unique value of the grouped fields. In this case you'll get the max date for each unique StudentID/ReceivedOnATBEligible/NotATBeligible triple, so if student #1 has two records where NotATBeligible is true in one and false in another, you'll get two maximum dates.

It seems what you want is one record per student, namely the one with the maximum date, plus whatever the ReceivedOnATBEligible and NotATBeligible values are on that record. For that you need two queries.

The first query -- call it StudentsMaxDates -- just gets each student plus their max dates:

SELECT tblComments.StudentID, Max(tblComments.CommentDate) AS MaxOfCommentDate
FROM tblComments
GROUP BY tblComments.StudentID;

The second query joins with StudentsMaxDates to get the other fields:

SELECT tblComments.*
FROM tblComments INNER JOIN [B]StudentsMaxDates[/B]
ON (tblComments.CommentDate = [B]StudentsMaxDates[/B].MaxOfCommentDate)
     AND (tblComments.StudentID = [B]StudentsMaxDates[/B].StudentID
     );