Question 20630 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search