SQL Queries||How many ways to get details of department-wise highest salary?
ฝัง
- เผยแพร่เมื่อ 18 ก.ย. 2024
- Non-Corelated Sub-Query:
select * from emp WHERE sal IN(select MAX(sal) From emp group by dept)
Corelated Sub-Query:
select * from emp e WHERE sal=(select MAX(sal) from emp e1 WHERE e1.dept=e.dept)
Sub-query With Join:
select * from emp e JOIN (select dept, MAX(sal) AS Max_Sal From emp Group By dept) e1
ON e.dept=e1.dept AND e.sal=e1.Max_sal
CTE with Rank Function:
With RankedSal AS (
select *, Row_number() OVER (PARTITION BY dept Order By Sal DESC)rn
From emp)
select * from RankedSal WHERE rn=1