If we have a table named Employees which contains columns emp_name, dept_name, year and salary.
Following are the row headers and values: (emp_name, dept_name, year, salary) (Nick, sales, 2020, 20000), (Peter, manager, 2020, 30000), (Nick, sales, 2021, 22000), (Peter, manager, 2021, 35000), (Sam, sales, 2022, 25000), (David, manager, 2022, 40000)
Now I want to select emp_name, dept_name, year and salary, and group by dept_name, emp_name. But I want distinct emp_name with max salary.
I tried a query:
select emp_name, dept_name, salary from Employees group by dept_name, emp_name, salary having max(salary);
output headers and values: (emp_name, dept_name, year, salary), (Nick, sales, 2020, 20000), (Nick, sales, 2021, 22000), (Peter, manager, 2020, 30000), (Peter, manager, 2021, 35000), (Sam, sales, 2022, 25000), (David, manager, 2022, 40000)
This is not the expected output.
Expected output: emp_name dept_name year salary Nick sales 2021 22000 Sam sales 2021 25000 Peter manager 2022 35000 David manager 2022 40000
Can someone help with the query?
