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

by amiya

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


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 an Oracle PLSQL cursor FOR LOOP which fetches the 3 highest values from the table, discards the first two records returned and processes 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 query and then the number of rows returned is limited by use of the Oracle pseudo column ROWNUM 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.

You might also be interested in this question and answer about using subqueries in Oracle and this tutorial on subqueries.

See our Oracle training page if you'd like formal training either on-line or in person.

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

Average Rating starstarstarstarstar

Click here to add your own comments

Feb 02, 2011
Rating
starstarstarstarstar
Another alternative using just SQL is to use DENSE_RANK
by: Solomon Yakobson

Just use analytic DENSE_RANK:

SQL> select distinct sal
2 from emp
3 order by 1 desc
4 /

SAL
----------
5000
3000
2975
2850
2450
1600
1500
1300
1250
1100
950

SAL
----------
800

12 rows selected.

SQL> select sal
2 from (
3 select sal,
4 dense_rank() over(order by sal desc) drnk
5 from emp
6 )
7 where drnk = &Nth_Highest
8 and rownum = 1
9 /
Enter value for nth_highest: 1
old 7: where drnk = &Nth_Highest
new 7: where drnk = 1

SAL
----------
5000

SQL> /
Enter value for nth_highest: 2
old 7: where drnk = &Nth_Highest
new 7: where drnk = 2

SAL
----------
3000

SQL> /
Enter value for nth_highest: 3
old 7: where drnk = &Nth_Highest
new 7: where drnk = 3

SAL
----------
2975

SQL>

Sep 22, 2010
Rating
starstarstarstarstar
An alternative solution using pure SQL
by: SATYENDRA SHUKLA

select rownum rank,last_name,salary
from
(
select rownum r,last_name,salary from employees order by salary desc
)
where r<=3
minus
select rownum rank,last_name,salary
from
(
select rownum r,last_name,salary from employees order by salary desc
)
where r=1;

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.