i want to better query ?
SELECT mb1.member_name, mb1.department_id, mb1.member_salary AS max_salary_department FROM member AS mb1 INNER JOIN (SELECT mb.department_id, MAX(mb.member_salary) AS member_salary FROM member AS mb GROUP BY mb.department_id) AS tb1 ON tb1.department_id = mb1.department_id AND tb1.member_salary = mb1.member_salary GROUP BY mb1.member_id ORDER BY mb1.department_id
I want output
- max salary of department
- name , salary , department
- if department max salary > 1 member show all member max salary