How to get third highest salary from a table without using top keyword ?

Data Base used for the query processing :- 

Table Name Employee
pkidNameSalary
1apekshit5000
2gaurav6000
3sunil7000
4rajnish6500
5amit8000

For finding 3rd highest salary from a table without using TOP, GROUP BY AND ROW COUNT or any other SQL Keywords,

There are two options for this :-

1. OPTION

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN
(SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY
NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)
UNION
(SELECT MAX(SALARY) FROM EMPLOYEE))

2. OPTION 

SELECT MAX(SALARY) FROM EMPLOYEE as E1 
WHERE 3=(SELECT COUNT(DISTINCT SALARY) 
FROM EMPLOYEE E2 WHERE E1.SALARY<=E2.SALARY)

No comments:

Post a Comment