Following is SQL statement’s Logical Order of Operations (not particular to SQL Server):
- GROUP BY
- Aggregations (COUNT, MAX, etc)
- UNION, INTERSECT, EXCEPT
- ORDER BY
- LIMIT, FETCH, TOP
That is why, running following SQL statement in SQL Server:
SELECT FirstName, LastName, COUNT(*) FROM dbo.User GROUP BY FirstName
Column ‘dbo.User.LastName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
GROUP BY is run first and grouped User’s columns by FirstName therefore LastName columns and all other columns are not visible by
Another not-so-obvious example:
SELECT FirstName, COUNT(*) FROM dbo.User WHERE COUNT(*) > 0 GROUP BY FirstName
Refer to logical order above, when
WHERE clause is executed,
COUNT(*) > 0 is not yet evaluated and thus SQL Server throw exception.