Friday, January 11, 2013

Retrieve third highest value from a table


Question: How can I retrieve the third highest salary amount from a salary table?
Answer: To retrieve the third highest salary from a salary table, you could run the following query: (please note that the subquery is sorted in descending order)
SELECT salary_amount
FROM (select salary2.*, rownum rnum from
             (select * from salary ORDER BY salary_amount DESC) salary2
      where rownum <= 3 )
WHERE rnum >= 3;
If you wanted to retrieve all fields from the salary table for the third highest salary, you could run the following query: (please note that the subquery is sorted in descending order)
SELECT *
FROM (select salary2.*, rownum rnum from
             (select * from salary ORDER BY salary_amount DESC) salary2
      where rownum <= 3 )
WHERE rnum >= 3;