Aug 20, 2013

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Suppose I have table named tblEmp:

CREATE TABLE [dbo].[tblEmp](
  [ntEmpID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [vcName] [varchar](100) NULL,
  [vcMobieNumer] [varchar](15) NULL,
  [vcSkills] [varchar](max) NULL,
  [moSalary] [money] NOT NULL,
  [ntLevel] [bit] NOT NULL
)

If we will execute this query:

SELECT
   moSalary,
   (SELECT TOP(1) vcName FROM tblEmp WHERE moSalary = MAX(moSalary))
FROM tblEmp E
GROUP BY moSalary

We will get error message:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Cause: If we don't reference any column in inner query then it is considered and column of inner table and we cannot user aggregate function MAX with the column of inner query:

SELECT TOP(1) vcName
FROM tblEmp 
WHERE moSalary = MAX(moSalary)

Since there is not any group by clause in the inner query.

Solution:

SELECT
   moSalary,
   (SELECT TOP(1) vcName FROM tblEmp WHERE moSalary = MAX(E.moSalary))
FROM tblEmp E

GROUP BY moSalary 

No comments:

Post a Comment