CODEWITHSUNDEEP
mysqlsql
Sierra Walker
Sierra Walker

Reputation: 106

SQL query, picking groups of one column based on another column

I am working with a table of employees and information in the format (Fname, Lname, Ssn, Bdate, Salary, Dno) Where Dno is the department number the employee works in

I am trying to create a query that returns the names of the employees that work in the same department as the person with the highest salary. I know I need to group by Dno but how do I sort my groups based on another column.

I have been trying things like

SELECT Fname, Lname
FROM EMPLOYEES
GROUPBY Dno
HAVING max(salary) in (dno)

I also tried doing nested Selects but I cannot seem to get the right ordering. I am new to SQL so any suggestions appreciated I understand its probably really simple I just cannot conceptualize it.

Upvotes: 2

Views: 110

Answers (2)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

The answer from barmar solves the problem; However, what if you want to see the rank of each employee by salary in each department. Another solution is to use a common table function to calculate rank. Then select from this result any employees who are number one.

with cte_emp_salaries as
(
select 
  fname, 
  lname, 
  salary,
  row_number() over (partition by dno order by salary desc) as salary_rank
from employees
)
select * from cte_emp_salaries where salary_rank = 1

You can leave off the where clause to see the whole ranking of the company by employee, department and salary.

Upvotes: 1

Barmar
Barmar

Reputation: 780889

Write a subquery that gets the department number of the employee with the maximum salary:

SELECT dno
FROM Employees
ORDER BY salary DESC
LIMIT 1

Then you can use that to filter the employees in the same department.

SELEECT Fname, Lname
FROM Employees AS e
JOIN (
    SELECT dno
    FROM Employees
    ORDER BY salary DESC
    LIMIT 1
) AS m ON e.dno = m.dno

Upvotes: 1

Related Questions