Find employee with MAX salary department wise without using RANK or DENSE_RANK
This can be achieved using the MAX function by extending the above query. The employee with MAX salary will have a SAL_DIFF as 0.
SELECT * FROM(
SELECT Employee_Id,
First_Name,
Department_Id,
Salary,
MAX(Salary) OVER(PARTITION BY Department_Id ) as MAX_SAL,
(MAX(Salary) OVER(PARTITION BY Department_Id )-Salary ) as SAL_DIFF
FROM EMPLOYEES)
WHERE SAL_DIFF = 0
