SQL: HAVING clause

See th开发者_如何学Ce 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.

As already pointed out, yes, that is the effect. For completeness, 'HAVING' is like 'WHERE', but for the already aggregated (grouped) values (such as, MAX in this case, or SUM, or COUNT, or any of the other aggregate functions).

Yes, it would exclude those rows.

Yes, that is what it would do.

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...

You may be trying the wrong thing with your MAX. By MAXing the invoice.date column you are effectively looking for the most recent invoice associated with the customer. So effectively the HAVING condition is selecting all those customers who have not had any invoices within the last 365 days.

Is this what you are trying to do? Or are you actually trying to get all customers who have at least one invoice from more than a year ago? If that is the case, then you should put the MAX outside the datediff function.

That depends on whether you mean rows in the table or rows in the result. The having clause filters the result after grouping, so it would elliminate customers, not invoices.

If you want to filter out the new invoices rather than the customers with new invoices, you should use where instead so that you filter before grouping:

select
  datediff("d",
  max(invoice.date), Now) As Date_Diff,
  max(invoice.date) as max_invoice_date,
  customer.number
from
  invoice 
  inner join customer on invoice.customer_number = customer.number
where
  datediff("d", invoice.date, Now) > 365
group by
  customer.number

I wouldn't use a GROUP BY query at all. Using standard Jet SQL:

  SELECT Customer.Number
  FROM [SELECT DISTINCT Invoice.Customer_Number
     FROM Invoice
     WHERE (((Invoice.[Date])>Date()-365));]. AS Invoices 
  RIGHT JOIN Customer ON Invoices.Customer_Number = Customer.Number
  WHERE (((Invoices.Customer_Number) Is Null));

Using SQL92 compatibility mode:

  SELECT Customer.Number
  FROM (SELECT DISTINCT Invoice.Customer_Number
     FROM Invoice
     WHERE (((Invoice.[Date])>Date()-365));) AS Invoices 
  RIGHT JOIN Customer ON Invoices.Customer_Number = Customer.Number
  WHERE (((Invoices.Customer_Number) Is Null));

The key here is to get a set of the customer numbers who've had an invoice in the last year, and then doing an OUTER JOIN on that result set to return only those not in the set of customers with invoices in the last year.