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;