I have the following code (see below) that finds the minimum date of birth of an employee.
I don't understand why does it throw the error:
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.
The following code throws the previous error:
SELECT *
FROM [TerpsConsultant.Employee] e1
WHERE EXISTS (
SELECT *
FROM [TerpsConsultant.Employee] e2
WHERE e1.empDateOfBirth = MIN(e2.empDateOfBirth)
)
After searching for similar questions on here, I tried this code and it worked fine:
SELECT *
FROM [TerpsConsultant.Employee] e1
WHERE EXISTS (
SELECT *
FROM [TerpsConsultant.Employee] e2
WHERE e1.empDateOfBirth = (SELECT MIN(e2.empDateOfBirth) FROM [TerpsConsultant.Employee] e2)
)
Would you help me understand why the first version of the code is not working? What's the difference after all?