This content has been marked as final. Show 2 replies
Search for Top-N Query and you'll get lots of examples, such as
Most of the examples use either the analytic RANK or ROW_NUMBER functions. If you're really interested in the 5 highest salaries (rather than, say, the 5 highest paid employees) then you probably want the DENSE_RANK function instead.
If you have trouble, post your best attempt here, with a specific question (including any error message).
If your query doesn't use commonly available tables (like those in the scott or hr schemas), then post CREATE TABLE and INSERT statements for some sample data. Either way, post the results you want from that data.
Always say what version of Oracle you're using.
Here it is,
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) rn,
empno, ename, job, mgr, hiredate, sal, comm, deptno
WHERE rn = 1;
Where rn=1 lists all the top employees in Each Department.
For yours, where rn<=5 will list the top 5 Employees in each Dept.