Here is the solution for nth highest
salary from employees table
SELECT FIRST_NAME , SALARY FROM
(SELECT FIRST_NAME, SALARY, DENSE_RANK() OVER
(ORDER BY SALARY DESC) AS SALARY_RANK
FROM EMPLOYEES)
WHERE SALARY_RANK = n;
select * from(
select ename, sal, dense_rank()
over(order by sal desc)r from Employee)
where r=&n;
To find to the 2nd highest sal set n = 2
To find 3rd highest sal set n = 3 and so on.
-- creating Employee table in Oracle
CREATE TABLE Employee (name varchar(10), salary int);
-- inserting sample data into Employee table
INSERT INTO Employee VALUES ('Rick', 3000);
INSERT INTO Employee VALUES ('John', 4000);
INSERT INTO Employee VALUES ('Shane', 3000);
INSERT INTO Employee VALUES ('Peter', 5000);
INSERT INTO Employee VALUES ('Jackob', 7000);
/*
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP N salary FROM Employee ORDER BY salary DESC
) AS temp
ORDER BY salary
*/
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 1 salary FROM Employee ORDER BY salary DESC
) AS temp
ORDER BY salary
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 2 salary FROM Employee ORDER BY salary DESC
) AS temp
ORDER BY salary
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary FROM Employee ORDER BY salary DESC
) AS temp
ORDER BY salary