Thursday, September 23, 2010

How to find top n salaries in Oracle table?

create table emp (empno number(6) primary key, ename varchar2(20), sal number(8));

insert into emp values(100,'aaa',4000);
insert into emp values(200,'bbb',1000);
insert into emp values(300,'ccc',5000);
insert into emp values(400,'ddd',3000);
insert into emp values(500,'eee',400);

-- Using Row Number
SELECT *
FROM (
SELECT * FROM emp ORDER BY sal DESC
)
WHERE ROWNUM <= 3;

-- Using oracle rank() Function
SELECT *
FROM ( SELECT empno,ename,sal,RANK() OVER (ORDER BY sal DESC) sal_rank
FROM emp )
WHERE sal_rank <= 2;

No comments:

Post a Comment