Highest Salary in each department

0 votes

I have a table EmpDetails:

DeptID      EmpName   Salary
Engg        Sam       1000
Engg        Smith     2000
HR          Denis     1500
HR          Danny     3000
IT          David     2000
IT          John      3000

I want to make a query that find the highest salary for each department.

Sep 18, 2022 in Database by Kithuzzz
• 38,010 points
1,876 views

1 answer to this question.

0 votes

SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID

The accepted response to the preceding question does not apply to the following circumstance. Let's imagine we need to identify the workers who earn the most money in each department for the table below.

DeptID
EmpName
Salary
Engg
Sam
1000
Engg
Smith
2000
Engg
Tom
2000
HR
Denis
1500
HR
Danny
3000
IT
David
2000
IT
John
3000

Take note that both Smith and Tom work in the engineering department and earn the highest wage there is. Because MAX() only provides a single number, the query "SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID" will fail. The following query is valid.

SELECT DeptID, EmpName, Salary FROM EmpDetails WHERE (DeptID,Salary) IN (SELECT DeptID, MAX(Salary) FROM EmpDetails GROUP BY DeptID)

Output will be

DeptID
EmpName
Salary
Engg
Smith
2000
Engg
Tom
2000
HR
Danny
3000
IT
John
3000
answered Sep 19, 2022 by narikkadan
• 63,700 points

Related Questions In Database

0 votes
1 answer

SQL query to find third highest salary in company

The easiest way to work this out ...READ MORE

answered Feb 21, 2022 in Database by Neha
• 9,060 points
45,630 views
0 votes
1 answer

What is the SQL query to get the third highest salary of an employee from employee_table

You can try out something like this: SELECT ...READ MORE

answered Sep 27, 2018 in Database by Sahiti
• 6,370 points
11,763 views
0 votes
1 answer

Do I need to add primary key in each and every table?

Hi Shraddha, You have a doubt to add ...READ MORE

answered May 29, 2019 in Database by sampriti
• 1,120 points
1,211 views
0 votes
1 answer

Query to list number of records in each table in a database

Use the below code to list number of ...READ MORE

answered Oct 11, 2019 in Database by Omaiz
• 560 points
1,279 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,060 points
637 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4, 2022 in Database by Vaani
• 7,060 points
346 views
0 votes
1 answer

What is a stored procedure?

A stored procedure is a set of ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
847 views
0 votes
1 answer

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

At the top level there are mainly ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
1,718 views
0 votes
1 answer

How to find top three highest salary in emp table in oracle?

Use this: SELECT *FROM ...READ MORE

answered Sep 18, 2022 in Database by narikkadan
• 63,700 points
7,627 views
0 votes
1 answer

How do I run an sql query in php?

Try this: <?php $conn = new mysqli('localhost', 'jaydeep_mor', 'jaydeep_mor', ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,700 points
565 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP