how to write sql query for max salary of employee with employee name

Question

1.00/5 (1 vote)

See more:

SQL

i have table with column employee name , dept, salary

i want to get max salary in each dept
can any one help me

Posted 6-Feb-15 6:08am

prabhu2210

Add a Solution

Comments

CHill60 6-Feb-15 12:09pm

What have you tried already and where are you stuck?

Richard Deeming 6-Feb-15 13:29pm

You're obviously taking the same course as chand5055[^]:
How to get employee names along department names who having highest salary in respective departments.[^]

Have you spent the time since your homework was set trying to find an answer yourself, or have you just left your homework until the last minute?

3 solutions

Solution 2

Try this:

SQL

select  name, dept, salary from table1 t1
where salary = (
 select max(salary) from table1 t2 where t1.dept=t2.dept
)

Posted 6-Feb-15 6:27am

Peter Leow

Solution 3

select name,salary from tblname where salary in (select MAX(salary) from tblname)

Posted 11-Feb-15 9:53am

PallavSingh

Updated 11-Feb-15 9:58am

v5

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-02-06T06:15:00

Solution 1

Try:

SQL

SELECT EmpName, Dept, Salary FROM
       (SELECT  *, ROW_NUMBER() OVER (PARTITION BY Dept Order By Salary DESC) AS rn
          FROM    mytable4) x
WHERE   rn = 1

Posted 6-Feb-15 6:15am

OriginalGriff

Comments

prabhu2210 8-Feb-15 0:57am

it works fine but i need explanation abt SELECT *, ROW_NUMBER() OVER (PARTITION BY Dept Order By Salary DESC.. what is row_number()

OriginalGriff 8-Feb-15 2:17am

Is Google not working where you are?
https://msdn.microsoft.com/en-us/library/ms186734.aspx
http://www.codeproject.com/Articles/308281/How-to-Use-ROW-NUMBER-to-Enumerate-and-Partition-R