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

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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0