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
)