Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Posts
    40

    Unanswered: 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,


    K

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The 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

  3. #3
    Join Date
    May 2003
    Posts
    40
    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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Smells Ok to me, but I can't test it to know for sure. Give it a shot and see what you get!

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good catch, I missed that!

    -PatP

  7. #7
    Join Date
    Mar 2004
    Posts
    28

    Re: Aggregate function MAX

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •