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
)
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