Re: the "having" clause

From: Amy (XXXNOSPAMXXX___l.a_at_usa.com)
Date: 03/08/04 

Date: Mon, 8 Mar 2004 12:15:53 +0200

Hi toylet,

Logically, The SELECT list is processed after the GROUP BY clause and the
HAVING filter.
So, When SQL Server processes the HAVING clause, the column alias is not yet
'known'.
The only place you can use column aliases you define in the select list is
in the ORDER BY clause which is processed last.
This is not a SQL Server 'freak behaviour', it is by design and supports the
ANSI rules for query processing order.
Search this forum for an article by Celko called 'Select Sequence' in which
he explains the whole thing very clearly.
All you should do is change your query to:

> select xx.pk, xx.amount, count(*) as recno
> from tx xx, tx yy
> where xx.pk>=yy.pk
> group by xx.pk, xx.amount
> having count(*) = 1

HTH

Amy

"toylet" <toylet_at_mail.hongkong.com> wrote in message
news:uHOOlNPBEHA.3472@TK2MSFTNGP09.phx.gbl...
> Why did SQL Server complain about "invalid column recno" in the
> following query? It worked in another database tool I am using.
>
> select xx.pk, xx.amount, count(*) as recno
> from tx xx, tx yy
> where xx.pk>=yy.pk
> group by xx.pk, xx.amount
> having recno = 1
>
> --
> .~. Might, Courage, Vision. In Linux We Trust.
> / v \ http://www.linux-sxs.org
> /( _ )\ Linux 2.4.22-xfs
> ^ ^ 5:52pm up 2 days 2:02 load average: 0.99 0.97 0.96

Relevant Pages

  • Re: The Many Flavors of SQL - Can a SQL Server query work in MS Access?
    ... One thing is that if you have more than one join in the From clause you have to use Parentheses ... You might try building the base of the query using the query grid. ... inner join cmsopen.dbo.apm_vendor v ... in the Query Analyzer on a Windows 2000 Server box running SQL Server ...
    (microsoft.public.access.queries)
  • Re: Maximum number of elements ina an IN() Clause
    ... if the IN clause is the ONLY criteria... ... If you are using MS SQL Server, can you define an heterogenous server, on it, pointing to an Access-Jet database, and push the values in a table there? ... Vanderghast, Access MVP ... would making the passtrough query WITHOUT the in clause and then, ...
    (microsoft.public.access.queries)
  • Re: ORDER BY in VIEW not working
    ... SQL Server is NOT guaranteed to repect an ORDER BY ... in the view unless you use ORDER BY also when you *query* the view. ... CREATE VIEW TopView AS ... Although the view definition contains an ORDER BY clause, ...
    (comp.databases.ms-sqlserver)
  • Re: Can anyone help with Data Designer issue?
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... debug what the command is sending to the Access database for any ... This query uses parameters and I'm afraid it's not sending the where ... as soon as I put in this where clause: ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Can anyone help with Data Designer issue?
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... debug what the command is sending to the Access database for any ... This query uses parameters and I'm afraid it's not sending the where ... as soon as I put in this where clause: ...
    (microsoft.public.dotnet.framework.adonet)