-1

I have made a view of 2 tables - Department Master and Employee Master. This is the table 

Emp_Id Emp_Name Salary   Dept_Id Dept
1      Chandan  10000.00 1       Mechnical
2      Sudhir   11000.00 1       Mechnical
3      Rahul    20000.00 1       Mechnical
4      Kavish   15000.00 1       Mechnical
5      sapin    23000.00 2       Computer
6      Kavita   23200.00 2       Computer
7      amit     50000.00 2       Computer

I want to get Maximum salary with department name and Employee Name I used this this group by query as follows 

select MAX(Emp_Salery) as Emp_Sal from V_New_Emp_Master group by Dept_Id

I am getting max salery , but when when I add Emp_Name column I get an error as followes

SQL query 

select MAX(Emp_Salery),Emp_Name as Emp_Sal from V_New_Emp_Master group by Dept_Id

Error:

Msg 8120, Level 16, State 1, Line 1
Column 'V_New_Emp_Master.Emp_Name' is invalid in the select list 
because it is not contained in either an aggregate function or the 
GROUP BY clause.

Is there any solution for this?

A ツ
  • 1,267
  • 2
  • 9
  • 14
can123
  • 3
  • 1
  • 1
    the msg says it all: you never group by emp_name, so which emp_name should sql server select out of 7? – A ツ Apr 18 '15 at 12:52
  • I found the solution. We require complex sub query. The solution is as followes select Emp_Name, Dept_Name, Emp_Salery from V_New_Emp_Master where Emp_Salery = (select max(Emp_Salery) from V_New_Emp_Master as f where f.Dept_Id = V_New_Emp_Master.Dept_Id); – can123 Apr 18 '15 at 13:02
  • possible duplicate of [What does the "invalid, not contained in either an aggregate function" message mean?](http://stackoverflow.com/questions/18258704/what-does-the-invalid-not-contained-in-either-an-aggregate-function-message-m) – Tanner Apr 29 '15 at 13:36

2 Answers2

1

you use either the subquery you found yourself:

select Emp_Name, Dept_Name, Emp_Salery 
from V_New_Emp_Master 
where Emp_Salery = ( select max(Emp_Salery) from V_New_Emp_Master as f where f.Dept_Id = V_New_Emp_Master.Dept_Id);

or go with a top 1 (which would be preferable if V_New_Emp_Master a view which needs some time to execute) :

select top 1 
       Emp_Name, Dept_Name, Emp_Salery 
from V_New_Emp_Master 
order by Emp_Salery desc

or if you need maybe the top salary per Dept_Id:

select Emp_Name, Dept_Name, Emp_Salery, Dept_Id
from (
   select *
        , max(Emp_Salery) over (partition by dept_id) max_Salery
   from V_New_Emp_Master 
) src
where Emp_Salery=max_Salary
A ツ
  • 1,267
  • 2
  • 9
  • 14
0

in ms sql server aggregate function queries, you can only include a column in select only if the column itself is included in an aggregate function or if the column is used to group the rows in the group by clause. In your case you will have to structure your query to a complex query with an aggregate function query as a sub query.

something like,

select Emp_Name,Emp_Salery from V_New_Emp_Master where Emp_Salery=(select MAX(Emp_Salery) from V_New_Emp_Master group by Dept_Id Error );

I haven't checked the query but it is definitely something like this, hope this turns you in the right direction.

javabot
  • 173
  • 10