Re: MIN - returns nothing?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: TomB (shuckle_at_hotmailXXX.com)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 11:07:23 -0400

Thanks. I had no idea why there even was a Having since it "seemed" to do
the same as Where.

Your explanation, explains why my query was working on some tests but not
others.

Thanks again.

Tom B

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%239baYauJEHA.2776@TK2MSFTNGP12.phx.gbl...
> TomB wrote:
> > SELECT MIN(StartTime)
> > FROM tblLocationBookings
> > HAVING (MIN(StartTime) > CONVERT(DATETIME, '4/20/2004 10:30:00
> > AM', 102))
> >
> > I expect the above to return the next "Booking" after 10:30 am on
> > 4/20/2004.
>
> Let's examine what this query does:
>
> It first calculates the MIN(StartTime). It then compares that calculated
> value to '4/20/2004 10:30:00 AM' (the CONVERT is not necessary - but date
> literals should be supplied in YYYYMMDD format to avoid ambiguity).
>
> So in this case, the min(StartTime) is likely to be less than the compared
> datetime in your HAVING clause, so nothing is returned.
>
> What you really want to do is filter the data used to calculate the min
> value BEFORE the min value is calculated. That means you need to use the
> WHERE clause:
>
> SELECT MIN(StartTime)
> FROM tblLocationBookings
> WHERE StartTime > '20040420 10:30:00 AM'
>
>
> WHERE filters before aggregates are calculated. HAVING filters AFTER
> aggregates are calculated. Here is an example where HAVING would be
useful.
> Say yhou wanted to see booking starttimes by location, but only for
> locations whose next booking is after '20040420 10:30:00 AM'. This query:
>
> SELECT LocationID,MIN(StartTime)
> FROM tblLocationBookings
> GROUP BY LocationID
>
> Might produce these results:
> 1 2/25/2004 9:30 AM
> 2 4/21/2004 9:30 AM
> 3 4/1/2004 10:30 AM
> 4 3/29/2004 9:00 AM
>
> This modification:
> SELECT LocationID,MIN(StartTime)
> FROM tblLocationBookings
> GROUP BY LocationID
> HAVING Min(StartTime) > '20040420 10:30:00 AM'
>
> will yield this result:
> 2 4/21/2004 9:30 AM
>
> HTH,
> Bob Barrows
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Relevant Pages

  • Re: MIN - returns nothing?
    ... Let's examine what this query does: ... It then compares that calculated ... WHERE filters before aggregates are calculated. ... GROUP BY LocationID ...
    (microsoft.public.inetserver.asp.db)
  • Re: Report not updating after splitting database
    ... I do have some that are filtered by dates, but the main query has no filters ... This author may have received products and services mentioned ... This main query does not have any filters on it. ... Is there a chance that the reports are based on queries that have ...
    (microsoft.public.access.reports)
  • Re: Interesting one
    ... Problem arose in a query which used an IN statement, ... you can check and onmode -g con and see it ... One problem I see is that the filter on column2 in the original query are in the WHERE clause and not in the ON clause. ... ON clause filters are applied pre-join and WHERE clause filters are applied post-join according to the new ANSI SQL rules. ...
    (comp.databases.informix)
  • Re: Obtaining a filter from a form
    ... using the query grid. ... Access MVP 2002-2005, 2007-2008 ... After a user has implemented various filters on various fields on ... that the user has chosen to display. ...
    (microsoft.public.access.queries)
  • RE: Interesting one
    ... The query hangs when running, you can check and onmode -g con and see it ... perhaps require a temp table, or through the INNER JOIN - god only knows ... are in the WHERE clause and not in the ON clause. ... ON clause filters are ...
    (comp.databases.informix)