3rd highest salary in oracle

Question

I had been looking for the query to find the 3rd highest salary from the database (using Oracle database). I found the below query -

SELECT *
FROM
  ( SELECT e.*, row_number() over (order by sal DESC) rn FROM emp e
  )
WHERE rn = 3;

I do not have oracle installed in my system, so I'm not try it out. But I want to know if the below query will work or not. If not, then why ?

WITH Sal_sort AS
  (SELECT DISTINCT sal FROM salary ORDER BY sal DESC
  )
SELECT * FROM Salary S, Sal_sort SS WHERE S.Sal = SS.Sal AND SS.rownum = 3;

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html download Express edition for testing. — Rob Allen, Jun 23 '14 at 17:43
You don't even have to install Oracle, you can also try this at http://sqlfiddle.com — Peter Lang, Jun 23 '14 at 18:02
This is a variant of the "select the nth row" question, http://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table. — Patrick Bacon, Jun 23 '14 at 18:15
"*I do not have oracle installed in my system*", go here: http://sqlfiddle.com — , Jun 23 '14 at 18:34

score 2 · Answer 1 · answered Jun 24 '14 at 05:11

Input Data

emp_no  emp_fname   emp_lname   salary
1   aa                      bb                      30  
2   ee                      yy                      31  
3   rr                      uu                      32  
4   tt                      ii                      33  
5   tt                      ii                      33  
6   tt                      ii                      33  
7   tt                      ii                      33  
8   tt                      ii                      30  
9   tt                      ii                      31

Example:

select * from ee;
select emp_no,salary ,dense_rank() over (order by salary  ) dr
from ee

Output

emp_no  salary  dr
1        30     1
8        30     1
9        31     2
2        31     2
3        32     3
4        33     4
5        33     4
6        33     4
7        33     4

score 0 · Answer 2 · edited Aug 07 '18 at 16:17

0

JUST ONE LINE

select * from (
select salary,dense_rank() over (order by salary desc) rank from  employees) where rank=3;

or

select * from (
select a.*,dense_rank() over (order by a.salary desc) rank from  employees a) where rank=3;

edited Aug 07 '18 at 16:17

tbone

15,107
3
33
40

answered Aug 02 '18 at 06:38

Laxmi Narayanan Lax

1

score 0 · Answer 3 · answered Aug 02 '18 at 12:20

So much easier in version 12 of the database and higher now.

SELECT *
  FROM employees
 ORDER BY salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

Tim talks about this feature here.

And if you take a look at the plan, you can see it's not magic, the optimizer is using analytic functions to derive the results.

score 0 · Answer 4 · edited Jun 13 '20 at 05:23

0

Without Dense_rank()

SELECT salary FROM employees
ORDER BY salary DESC
OFFSET 2
FETCH 1 NEXT ONE ROWS ONLY;

With Dense_rank()

SELECT salary 
FROM
(
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank from employees
)
WHERE rank = 3;

edited Jun 13 '20 at 05:23

Ardent Coder

3,777
9
27
53

answered Jun 13 '20 at 00:45

himanshu mishra

1
1

3rd highest salary in oracle

4 Answers4