Aggregate function MAX

Hi

I have a query where I need to use an aggregate function MAX in where clause, I know that MAX cannot be used in a Where clause because it is an aggregate function. Can anyone help me out in writing this query?

SELECT * FROM ACCOUNT_REVIEW AR INNER JOIN
QUESTION_RESPONSE ON
AR.Review_ID = QUESTION_RESPONSE.Review_ID
WHERE (MAX(AR.Review_Date) IS NULL)

I need it asap. Thanks in advance,

KThe WHERE clause takes effect at row selection time, so the aggregates aren't built yet. There is a similar HAVING clause that takes effect after the aggregation has been done. Do your comparison in the HAVING clause.

-PatP|||Pat

Is this syntax correct?

SELECT * FROM ACCOUNT_REVIEW INNER JOIN
QUESTION_RESPONSE ON ACCOUNT_REVIEW.Review_ID = QUESTION_RESPONSE.Review_ID
WHERE (ACCOUNT_REVIEW.Account_ID = '5053660')
group by account_review.date_completed,QUESTION_RESPONSE.Q_ ID, QUESTION_RESPONSE.Sub_Q_ID, QUESTION_RESPONSE.Sub_Choice_ID,
QUESTION_RESPONSE.Response_Text
having Max(Account_Review.Date_Completed) is null

Thanks|||Smells Ok to me, but I can't test it to know for sure. Give it a shot and see what you get!

-PatP|||Originally posted by kir441
Pat

Is this syntax correct?

SELECT * FROM ACCOUNT_REVIEW INNER JOIN
QUESTION_RESPONSE ON ACCOUNT_REVIEW.Review_ID = QUESTION_RESPONSE.Review_ID
WHERE (ACCOUNT_REVIEW.Account_ID = '5053660')
group by account_review.date_completed,QUESTION_RESPONSE.Q_ ID, QUESTION_RESPONSE.Sub_Q_ID, QUESTION_RESPONSE.Sub_Choice_ID,
QUESTION_RESPONSE.Response_Text
having Max(Account_Review.Date_Completed) is null

Thanks

It cannot be correct, even if it doesn't bomb with syntax error, because you're trying to get a MAX of a field while doing GROUP BY on it.|||Good catch, I missed that!

-PatP|||This is a normalized database, right? Why not leave it simple?

SELECT * FROM ACCOUNT_REVIEW AR INNER JOIN
QUESTION_RESPONSE QR ON
AR.Review_ID = QR.Review_ID

WHERE (AR.Review_Date IS NULL)

************************

Sigh. Alas, the world is not a perfect case. In that instance,
***************************
select distinct ar.*, qr.* from ACCOUNT_REVIEW AR INNER JOIN
QUESTION_RESPONSE QR ON
AR.Review_ID = QR.Review_ID

left outer join (select review_id, max(review_date) as maxdate from ACCOUNT_REVIEW AR2 group by review_date ) as maxtable

on AR.Review_id =maxtable.Review_id and maxtable.maxdate is null

*****************

Kind of hard to test this. Basic idea is to create a "maximum table" with list of key fields and the maximum date, then left outer join into it (I suppose it could be inner join) and grab only those rows where the max is a null.
good luck!