https://www.youtube.com/watch?v=7W7B0y5WsaQ&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=11

CREATE TABLE employee
(
emp_name VARCHAR(10),
dep_id int,
salary int
);

INSERT INTO employee (emp_name, dep_id, salary)
VALUES 
    ('Siva', 1, 30000),
    ('Ravi', 2, 40000),
    ('Prasad', 1, 50000),
    ('Sai', 2, 20000);

WITH
CASE WHEN

-- Approach 1
WITH CTE(dep_id,Highest_sal,Least_sal) AS(
SELECT dep_id,MAX(salary) AS Highest_sal,MIN(salary) AS Least_sal
FROM employee
GROUP BY dep_id)

SELECT e.dep_id, 
MAX(CASE WHEN salary = Highest_sal THEN emp_name ELSE NULL END) AS Max_Sal_Emp,
MAX(CASE WHEN salary = Least_sal THEN emp_name ELSE NULL END) AS Min_Sal_Emp
FROM employee e 
INNER JOIN CTE ON e.dep_id = cte.dep_id
GROUP BY dep_id

-- Max is used in both cases because we had to ignnore nulls in the columns.

ROW_NUMBER()

-- Approach 2
WITH CTE AS (
SELECT *,
row_number() OVER (PARTITION BY dep_id ORDER BY salary DESC) AS rank_desc,
row_number() OVER (PARTITION BY dep_id ORDER BY salary ASC) AS rank_asc
FROM employee)

SELECT dep_id,
MAX(CASE WHEN rank_desc = 1 THEN emp_name END )AS Highest_sal,
MAX(CASE WHEN rank_asc = 1 THEN emp_name END) AS Least_sal
FROM CTE
GROUP BY dep_id