Highest Salary Queries

       Let us take the below 2 tables to find Highest salaries.

          1. Highest salary in Employee Table

 

  2. Second Highest Salary

3. Nth Highest Salary
   SELECT SAL FROM
   (SELECT  SAL, ROW_NUMBER() OVER ( ORDER BY  SAL DESC ) RANKNO FROM EMPT
   ORDER BY 1 DESC)
   WHERE RANKNO = 5;
   
   
   
4. Department wise highest salary
   
   
   
   
   
5. Department wise 2^nd Highest Salary
   
   SELECT   deptid, MAX (sal) second_max
   FROM (SELECT e.*, MAX (sal) OVER (PARTITION BY deptid) maxsal
         FROM empt e)
   WHERE sal <> maxsal
   GROUP BY deptid;

6.  Department wise Nth highest salary

SELECT * FROM   
  (SELECT *,DENSE_RANK() OVER (PARTITION BY DeptId ORDER BY EmpSalary DESC)
     AS Rnk         
     FROM Employee     
  ) X 
WHERE  X.Rnk=3 

7. Department wise Nth highest salary with out using Analytical functions

select a.DEPTNO, a.SAL 

from EMP a
where 3 = (select COUNT ( distinct B.SAL ) from EMP B 

           where a.SAL <= B.SAL AND A.DEPTNO = B.DEPTNO)
;

8. Department wise top 3 highest salaries 

select deptno,empno,sal from (select e.* , dense_rank() over (partition by deptno order by sal desc) as rnk from emp e)
where rnk in (1,2,3)
order by 1 asc,3 desc;

9. Department wise top 3 highest salaries without using Analytical functions

SELECT a.DEPTNO,
  a.SAL
FROM EMP a
WHERE 3 <=
  (SELECT COUNT ( DISTINCT B.SAL )
  FROM EMP B
  WHERE a.SAL <= B.SAL
  AND A.DEPTNO = B.DEPTNO
  ) ;