SELECT ename, sal FROM ( SELECT ename, sal FROM emp ORDER BY sal DESC ) WHERE ROWNUM <= 3; ENAME SAL ---------- ---------- KING 5000 FORD 3000 SCOTT 3000but how do I find all the earners (three or more) of the top three salaries?
ENAME SAL ---------- ---------- KING 5000 FORD 3000 SCOTT 3000 JONES 2975
You gave a good example of a top-n query. The solution to this problem involves what you might call a top- n subquery:
SELECT ename, sal FROM emp WHERE sal >= ( SELECT MIN (sal) FROM ( SELECT DISTINCT sal FROM emp ORDER BY sal DESC ) WHERE ROWNUM <= 3 ) ORDER BY sal DESC, ename;Quiz question: Why can't we say "SELECT ... WHERE ROWNUM = 3"? Because, by definition, the first row selected will have 1 for a ROWNUM. If no row is selected unless ROWNUM = 3, then the query will never choose a first row. (I think of this as the Lake Wobegone paradox, named after the community where "all the children are above average".)
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.