CODEWITHSUNDEEP
sqlsql-servergroup-by
Queue113
Queue113

Reputation: 31

Highest salary per department (also same salary)

How can I select the highest salary on each department with a same salary.

My query is only to get the first row in each department with the same salary. But I want to select all max same salary on each department. Please help me out of this problem.

Below is the sample table:

PSD Department
----------------------
Yumang's Salary: $500
Paus Salary: $500

QA Department
----------------------
Villanueva: $1000 
Calacar: $1000

Here's the code I am trying: 

SELECT MAX(inter_department_votes.number_votes)
FROM employee_salary
GROUP BY dept_id

Upvotes: 0

Views: 216

Answers (3)

Aniket Kariya
Aniket Kariya

Reputation: 1960

your table isn't clear to me. i can't understand why you're creating separate tables for all departments.

assuming you make two different tables, one for employees and one for department. this will make queries simpler for future and efficient database. In that case:

+----------+------------+------+-----+
|EmployeeID|EmployeeName|Salary|DepNo|
+----------+------------+------+-----+
|                                    |
+----------+------------+------+-----+
+-----+-------+
|DepNo|DepName|
+-----+-------+
|             |
+-----+-------+


 SELECT DepName, EmployeeName, salary
 FROM Department d
 INNER JOIN Employee e on e.DepNo = d.DepNo 
 INNER JOIN 
 (
    SELECT DepNo, MAX(salary) sal
    FROM Employee
    GROUP BY DepNo
 ) ss ON e.DepNo = ss.DepNo
 AND e.salary = ss.sal;

Upvotes: 0

Ilya Berdichevsky
Ilya Berdichevsky

Reputation: 1288

Using RANK() function:

RANK provides the same numeric value for ties (for example 1, 1, 2, 4, 5).

SELECT *
FROM (
      SELECT dept_id, 
             PersonName, 
             Salary,
             RANK() OVER(PARTITION dept_id ORDER BY Salary DESC) AS SortBySalary 
      FROM employee_salary
     )
WHERE SortBySalary = 1

Also, see this answer using MAX() function.

Upvotes: 0

Amani
Amani

Reputation: 119

try selecting dept_id as well: 

SELECT dept_id, 
       MAX(inter_department_votes.number_votes) 
FROM employee_salary GROUP BY dept_id

Upvotes: 1

Related Questions