Need help with Oracle query

30 pts.
Tags:
Oracle development
Oracle queries
Query optimization
How to display the third highest salary from an employees table in Oracle. Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Previous to version 12c, Oracle doesn’t support any direct way to limit results, so you would have to do something like this:

select * from 
(
select * from 
(
select * from employees
order by salary desc
)
where rownum <= 3
order by salary
) where rownum = 1;

On 12c, the new row limiting clause can be used, to achieve the same result with a simpler query.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Removed this answer by vembutech from the answer wiki, since the LIMIT clause is not supported on Oracle.

    "SELECT salary  FROM (SELECT salary FROM Employee ORDER BY salary DESC LIMIT 3) ORDER BY salary LIMIT 2;"
    70,220 pointsBadges:
    report
  • AshishSingh10
    Hi, SELECT TOP salary FROM SELECT TOP 3 salary FROM employees ORDER BY DESC AS emp ORDER BY salary ASC In that code you can replace any value for 3rd. Thanks Ashish@S
    875 pointsBadges:
    report
  • carlosdl
    SELECT TOP ...  is not supported on Oracle.
    70,220 pointsBadges:
    report
  • Brijesh
    Select * from emp where sal in (Select max(sal) from emp where sal < (Select max(sal) from emp where sal < (Select max(sal) from emp )));
    9,870 pointsBadges:
    report
  • carlosdl
    At first sight, I believe Brijesh's last query would work.  It would get very ugly if instead of 3rd you want 10th highest salary, though.
    70,220 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following