SQL: "Column 'tbl.column' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Question

SQL: "Column 'tbl.column' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I was wondering why i'm getting the following error:
Column 'tbl.column' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
when the sql statement looks like:
SELECT tbl.column, MAX(tblOther.columnOtherId) AS otherID FROM (tbl INNER JOIN tblOther ON tbl.columnId = tblOther.columnOtherId) INNER JOIN tblOtherAgain ON tblOther.columnOtherId = tblOtherAgain.columnAgainOtherId WHERE tblOther.columnOtherAgainId = @id.

When I remove the aggregate function MAX on tblOther.columnOtherId I don't receive the above error. So how do I get the statement shown above to work without getting the shown error?

DBLibrary: Tedious.js

sql
sql-server
node.js

Answer 1

You have use an aggregate function MAX() and there is a field in the SELECT clause which is not aggregated that is why you need to have GROUP BY clause,

SELECT  tbl.column, 
        MAX(tblOther.columnOtherId) AS otherID 
FROM    (tbl INNER JOIN tblOther 
            ON tbl.columnId = tblOther.columnOtherId) 
        INNER JOIN tblOtherAgain 
            ON tblOther.columnOtherId = tblOtherAgain.SourceId 
WHERE   tblOther.columnOtherAgainId = @id
GROUP   BY tbl.column

Answer 2

SELECT tbl.column, MAX(tblOther.columnOtherId) AS otherID FROM (tbl INNER JOIN tblOther ON tbl.columnId = tblOther.columnOtherId) INNER JOIN tblOtherAgain ON tblOther.columnOtherId = tblOtherAgain.columnAgainOtherId WHERE tblOther.columnOtherAgainId = @id
GROUP BY tbl.column