Saturday, 9 April 2016

Find nth highest salary in sql



                                        Find Second Highest Pay of Employee Name


Table Name :-  Emp
         ID
 EMPLOYEE
   PAY
1
JOHN
25000
2
SMITH
28000
3
PHILIP
18000
4
MARKS
21000


Write SQL Query to get Second Highest Pay of Employee name From Above Table :

                                        

SELECT EMPLOYEE

FROM Emp

WHERE Pay NOT IN

(SELECT MAX (Pay) FROM Emp );



----------------------------------------------------------------------------------------------------------------

                                           Find nth Highest Pay of Employee Name 


 Table Name :- Emp
ID
Emp_name
Pay
1
JOHN
25000
2
SMITH
28000
3
PHILIP
18000
4
MARKS
21000
5
HARRY
19050
6
JORDAN
25050




Write a Query For finding nth highest Pay of Employee Name

SELECT Emp_name

FROM Emp outeremp

WHERE (N-1) = (  SELECT COUNT(DISTINCT (inneremp.Pay ) )

                                FROM emp inneremp

                                WHERE inneremp.Pay > outeremp.Pay

    )