Selecting Top 10 Employee Salaries

2019-09-05 16:55发布

问题:

I have a database for salaries of employees.

Here are the Salaries Table fields:

emp_no INT(11)
salary INT(11)
from_date DATE
to_date DATE

I'm trying to get the top 10 employee salaries, non-duplicate employees. With the code below I can get the maximum salary for the emp_no, but my from_date and to_date are defaulting to the earliest date values for that emp_no.

select emp_no, max(salary) as salary, from_date, to_date
from salaries
group by emp_no
order by salary DESC
limit 10

I assumed GROUP BY would keep the values of the dates that matched up with the salary and emp_no, but after thinking about it a bit it doesn't make sense for it to do so.

Any help with how to get the correct date values would be very much appreciated.

回答1:

First table will contain the top 10 salaries.

Then you check original table to see which row matches emp_no and salary to get dates.

SELECT S.*
FROM  
    (SELECT emp_no, max(salary) as salary
     FROM salaries
     GROUP BY emp_no
     ORDER BY salary DESC
     limit 10
   ) maxSal
INNER JOIN salaries S
   ON maxSal.emp_no = S.emp_no
  AND maxSal.salary = S.salary