Q
Problem solve Get help with specific problems with your technologies, process and projects.

Top-N Subquery

With an in-line view, I can get the top three earners:
SELECT  ename,
        sal
FROM    (
            SELECT    ename,
                      sal
            FROM      emp
            ORDER BY  sal     DESC
        )
WHERE   ROWNUM  <= 3;

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
but 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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close