SQL: HAVING clause

Question

See the following SQL statement:

SELECT datediff("d", MAX(invoice.date), Now) As Date_Diff
      , MAX(invoice.date) AS max_invoice_date
      , customer.number AS customer_number
FROM invoice 
    INNER JOIN customer 
        ON invoice.customer_number = customer.number
GROUP BY customer.number 

If the the following was added:

HAVING datediff("d", MAX(invoice.date), Now) > 365

would this simply exclude rows with Date_Diff <= 365?

What should be the effect of the HAVING clause here?

EDIT: I am not experiencing what the answers here are saying. A copy of the mdb is at http://hotfile.com/dl/40641614/2353dfc/test.mdb.html (no macros or viruses). VISDATA.EXE is being used to execute the queries.

EDIT2: I think the problem might be VISDATA, because I am experiencing different results via DAO.

Answer 1

WHERE applies to all of the individual rows, so WHERE MAX(...) would match all rows.

HAVING is like WHERE, but within the current group. That means you can do things like HAVING count(*) > 1, which will only show groups with more than one result.

So to answer your question, it would only include rows where the record in the group that has the highest (MAX) date is greater than 365. In this case you are also selecting MAX(date), so yes, it excludes rows with date_diff <= 365.

However, you could select MIN(date) and see the minimum date in all the groups that have a maximum date of greater than 365. In this case it would not exclude "rows" with date_diff <= 365, but rather groups with max(date_diff) <= 365.

Hopefully it's not too confusing...