How can I get the 2nd or the 3rd highest value from a table in an Oracle database?
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:
i PLS_INTEGER := 0;
FOR sal_recs IN
SELECT sal FROM emp ORDER BY sal DESC
i := i + 1;
IF i = 3 THEN <do whatever is needed> END IF;
EXIT WHEN i = 3;
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
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.