Query processing with HAVING - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query processing with HAVING

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-08-15 : 10:09:47

Hi,

does anybody know why I can't use an alias assigned in the select part of a query in the HAVING clause, just like one would do in an ORDER BY?

Works:

SELECT Group = CASE 
  WHEN a BETWEEN 1 AND 200 THEN 'A'
  WHEN a BETWEEN 201 AND 500 THEN 'B'
FROM ...
ORDER BY Group

Doesn't work:

SELECT Group = CASE 
  WHEN a BETWEEN 1 AND 200 THEN 'A'
  WHEN a BETWEEN 201 AND 500 THEN 'B'
FROM ...
HAVING Group = 'B'

A funny example maybe but ORDER BY and HAVING are both processed after the "main processing" is done which is why I don't understand that I can't use them in the same way.

- Lumbago

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-15 : 11:51:55

Alises can be directly used only in ORDER BY not in HAVING or GROUP BY caluses. You need make the query a derived table and then refer the alias as a field in HAVING/GROUP BY if you want to use it directly.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-08-18 : 04:26:41

I know how to work around it, I just think it's a little bit weird why the ORDER BY use "late" processing (isn't that what it's called...?) but the HAVING doesn't, when they both have to be processed after the "inner" resultset has been created.

- Lumbago

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-08-18 : 08:58:42

The clauses of a query are logically evaluated in a specific order. HAVING is evaluated before SELECT and ORDER BY is evaluated after SELECT.

From memory the order of evaluation goes something like:
FROM
INNER bits of JOINS
OUTER bits of JOINS
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP

eg http://tinman.cs.gsu.edu/~raj/sql/node22.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources