SQL query to find out the name and salary of the employee who received highest salary for each month-CodePudding

SQL Here you can see I write below code that gives output of id and salary but I want name and salary

CREATE TABLE salary_data (
      id INTEGER NOT NULL,
      month INTEGER NOT NULL,
      salary INTEGER NOT NULL
    );
    -- another TABLE
    CREATE TABLE emp_data (
        id INTEGER NOT NULL,
        name TEXT NOT NULL);
    -- insert some values
    INSERT INTO salary_data VALUES (1, 3,50000);
    INSERT INTO salary_data VALUES (2,4,45000);
    INSERT INTO salary_data VALUES (3,3,36000);
    INSERT INTO salary_data VALUES (4,5,72000);
    INSERT INTO salary_data VALUES (1,6,49000);
    INSERT INTO salary_data VALUES (4,4,51000);
    INSERT INTO salary_data VALUES (2,5,64000);
    INSERT INTO emp_data VALUES (1, "RAMESH");
    INSERT INTO emp_data VALUES (2, "SURESH");
    INSERT INTO emp_data VALUES (3, "NIKHIL");
    INSERT INTO emp_data VALUES (4, "RAJEEV");
    -- fetch some values
    SELECT id,max(salary) FROM salary_data GROUP BY month;

I think I am missing some in the last line enter code here

I want this type of output

name salary,

ramesh 50000

like that

CodePudding user response：

You did the first step and miss the second. You have found the highest salary per month. Now find the employees earning that much.

Here is one way to do that:

select *
from salary_data s
join emp_data e on e.id = s.id -- s.id is a misnomer for s.emp_id
where (s.month, s.salary) in
(
  select month, max(salary)
  from salary_data
  group by month
);

Here is another (only possible since MySQL 8):

select *
from
(
  select
    id as emp_id, month, salary, 
    max(salary) over (partition by month) as max_salary_for_the_month
  from salary_data
) s
join emp_data e on e.id = s.emp_id
where s.salary = s.max_salary_for_the_month
order by s.month;