Having problem with Group By clause in ms sql server query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

SQL

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

SQL

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

Error:

SQL

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

Thanks

Posted 18-Apr-15 2:16am

Chandan_N87

Updated 18-Apr-15 2:29am

Rajesh waran

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-04-18T02:35:00

When you group data together, you can only return the group clause and aggregate information such as a count or sum.
Think about it:

Name   PenColour  NoPens
Joe    Blue       2
Joe    Green      3
Mike   Blue       1
Mike   Red        4

When you GROUP these by name you get combined records:

Name   PenColour        NoPens
Joe    Blue and Green   2 and 3
Mike   Blue and Red     1 and 4

Clearly, you can't return a PenColour for Joe because there are two distinct values and only one row to return them in - And SQL deosn't know which one it should return. So you can return an aggregate of the other info in a group, but you can't return multiple values.

If you want to find the user with the maximum salary within each group, you need to use a more complex subquery.
See here - it explains it well: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/[^]