nth highest salary in Oracle using DENSE_RANK, Oracle

nth highest salary in Oracle using DENSE_RANK

drop table empsala\\

create table empsala
(
    id int,
    name varchar(255),
    salary int
)\\

insert into empsala values (1,'a',1000)\\
insert into empsala values (2,'b',2000)\\
insert into empsala values (3,'c',3000)\\
insert into empsala values (4,'d',1000)\\
insert into empsala values (5,'e',4000)\\
insert into empsala values (6,'f',4000)\\
insert into empsala values (7,'g',4000)\\
insert into empsala values (8,'h',3000)\\

select * from empsala\\

select id,name,salary,RANK() OVER (ORDER BY Salary DESC) as rn, DENSE_RANK() OVER (ORDER BY Salary DESC) as dense_rn  
from empsala \\

--dense_rank : 2nd highest salary in Oracle using DENSE_RANK
SELECT * FROM  (
SELECT id,name,Salary,DENSE_RANK() OVER (ORDER BY Salary DESC) as dense_rn FROM empsala
                )
WHERE dense_rn = 2\\

--rank : using rank try to fine 2nd highest salary in Oracle : nothing we get

SELECT * FROM  (
SELECT id,name,Salary,RANK() OVER (ORDER BY Salary DESC) as rn FROM empsala
                )
WHERE rn = 2\\


run \| edit \| history \| help	0