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 1,523 views

## 1 answer to this question.

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`
• 63,180 points

## SQL query to find third highest salary in company

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

## 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

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

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

## How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

## How do I UPDATE from a SELECT in SQL Server?

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

## What is a stored procedure?

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

## LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

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