Wednesday, September 2, 2015

How to find second or nth highest salary?

Interview Question:

Query:  To Find Second Highest Salary

Using RowId:
SELECT EMPLOYEE_ID,SALARY,RN FROM
(SELECT EMPLOYEE_ID,
SALARY,
ROW_NUMBER() OVER(ORDER BY SALARY DESC) RN
FROM EMPLOYEES)
WHERE RN =2;


Using Sub-Query:

SELECT EMPLOYEE_ID , SALARY
FROM EMPLOYEES Emp1
WHERE (2-1) = (
               SELECT COUNT(DISTINCT(Emp2.SALARY))
               FROM EMPLOYEES Emp2
               WHERE Emp2.SALARY> Emp1.SALARY
               )


Query: To Find nth Highest Salary

Using RowId:
SELECT EMPLOYEE_ID,SALARY,RN FROM
(SELECT EMPLOYEE_ID,
SALARY,
ROW_NUMBER() OVER(ORDER BY SALARY DESC) RN
FROM EMPLOYEES)
WHERE RN =<n>;


Using Sub-Query:

SELECT EMPLOYEE_ID , SALARY
FROM EMPLOYEES Emp1
WHERE (<n>-1) = (
               SELECT COUNT(DISTINCT(Emp2.SALARY))
               FROM EMPLOYEES Emp2
               WHERE Emp2.SALARY> Emp1.SALARY
               )


No comments:

Post a Comment