Home > Enterprise >  SQL - To find max and min salary from emp table along with emp name?
SQL - To find max and min salary from emp table along with emp name?

Time:11-27

I need to find max and min salary for each department along with emp name . Below is my sample dataset. enter image description here

Expected output is :

enter image description here

Please share your thoughts on this

CodePudding user response:

Use window functions:

SELECT DISTINCT Dept_id,
       MIN(Salary) OVER (PARTITION BY Dept_id) Min_Salary,
       FIRST_VALUE(Emp_Name) OVER (PARTITION BY Dept_id ORDER BY Salary) Min_salary_emp_name,
       MAX(Salary) OVER (PARTITION BY Dept_id) Max_Salary,
       FIRST_VALUE(Emp_Name) OVER (PARTITION BY Dept_id ORDER BY Salary DESC) Max_salary_emp_name
FROM tablename;
  • Related