How can I get the 2nd or the 3rd highest value from a table in an Oracle database?

by amiya

Question: For example, using the emp table in the sample schema in Oracle, how would I retrieve just the 2nd or 3rd highest salary?

Answer:
There are a couple of ways to do this in Oracle, depending on what it is exactly that you want.

You could do it programmatically using PL/SQL, for example as follows:

DECLARE
   i PLS_INTEGER := 0;

BEGIN

   FOR sal_recs IN
    (
      SELECT sal FROM emp ORDER BY sal DESC
    )
   LOOP

       i := i + 1;
       IF i = 3 THEN <do whatever is needed> END IF;
       EXIT WHEN i = 3;

   END LOOP;

END;

This uses a PLSQL cursor FOR LOOP which fetches the 3 highest values from the table, discarding the first two records returned and processing the third.

The other way to do it is use just run a pure SQL statement against your Oracle database, as follows:

SELECT MIN(sal) FROM
(
 SELECT sal FROM
   (
     SELECT DISTINCT sal
     FROM emp
     WHERE sal <
       (
         SELECT MAX(sal) FROM emp
       )
     ORDER BY sal DESC
   )
 WHERE ROWNUM < 3
)

This method creates an in-line view (SELECT DISTINCT sal FROM emp ...) which returns all the salaries less than the maximum salary which is determined by use of a correlated sub-query (SELECT MAX(sal) FROM emp).

Although these 2 approaches will achieve the same result in Oracle, there are subtle differences.


The Oracle PL/SQL approach just takes the 3rd record returned. As the records are sorted in descending order you know that this will be the 3rd highest salary.

But do you?

What you would actually do is process the 3rd record returned by the SQL query SELECT sal FROM emp ORDER BY sal DESC. If there are 2 or more employees with the same salary, this may not be quite what you want.

On the other hand, the pure SQL approach ignores additional records with the same highest salary and starts counting only from the first record with a salary lower than the maximum. If there are 2 or more records with the same salary, these will be removed from the results by use of the SQL keyword DISTINCT.

Note that the results are sorted in the inner SQL qeury and then the number of rows returned is limited by use of the ROWNUM pseudo column in the outer query.

Normally the number of rows returned is limited before the results are sorted. Doing it this way causes the results to be sorted and then limited, otherwise you would just fetch 2 records at random and then sort those 2 which is unlikely to give you the results you want.