Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have restored the Northwind database(https://northwinddatabase.codeplex.com/) from a backup file using SQL Server.

Trying to execute the following query results in error "Invalid column name 'products'.", product is an alias.

SELECT OrderID, COUNT(ProductID) products
FROM [NORTHWND].[dbo].[OrderDetails]
GROUP BY OrderID
HAVING products > 5;

What is the problem?

share|improve this question

migrated from serverfault.com Jan 22 at 15:10

This question came from our site for system and network administrators.

2  
MySQL does allow this and AFAIK it doesn't create any more ambiguity than does allowing it in the ORDER BY. However SQL Server supports window functions SELECT ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RN FROM SomeTable HAVING RN > 10 wouldn't make any sense at all as the ROW_NUMBER is calculated after the HAVING. – Martin Smith Jan 22 at 17:11
up vote 22 down vote accepted

The SELECT clause is logically processed after the HAVING clause. Therefore the aliases used in SELECT don't exist (yet) when the HAVING clause is processed.

On MSDN you can look at SELECT (Transact-SQL):

Logical Processing Order of the SELECT statement

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

This query works because it repeats what will later be defined (the COUNT) in the SELECT clause:

SELECT OrderID, COUNT(ProductID) products
FROM [NORTHWND].[dbo].[OrderDetails]
GROUP BY OrderID
HAVING COUNT(ProductID) > 5;
share|improve this answer
2  
+1. It's amazing that even the official documentattion doesn't get it entirely right (well, not so amazing but rather common for MSDN. It's at parts clumsily written and omits details). The JOIN and ON are part of the FROM clause and not independent clauses as the article implies. – ypercubeᵀᴹ Jan 22 at 16:25

Because the HAVING is parsed before the alias has been generated in the SELECT list. This is explained in much detail here.

The only place you could use an alias is in the ORDER BY clause. A workaround is to repeat the expression in the HAVING:

    SELECT OrderID, COUNT(ProductID) AS Products
      FROM Northwind.dbo.OrderDetails
      GROUP BY OrderID
      HAVING COUNT(ProductID) > 5;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.