[SOLVED] Msg 8120: Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Msg 8120: Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I have EMP table in SQL Server 2012:

CREATE TABLE EMP
(
    [ID] INT NOT NULL PRIMARY KEY,
    [MGR_ID] INT, 
    [DEPT_ID] INT, 
    [NAME] VARCHAR(30), 
    [SAL] INT, 
    [DOJ] DATE
)

When I am executing this query

Select NAME, MAX(SAL) as Salary FROM EMP;

I'm bumping into this error:

Msg 8120, Level 16, State 1, Line 1

Column 'EMP.NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please help

P.S.

Potentially the reason for the above-mentioned error is that I have multiple rows in the NAME column and MAX aggregate function returns only one row.

Comment on Gordon's answer:

I actually want to return a name of an Employee(s) with the second highest salary

Solution

Select Name, Max(sal) as salary from employee group by Name

So when you use any aggregate function in sql like count, max, min etc we have to specify those columns from select list which we want to be thw grouping column of result set