ORACLE sql query for getting top 3 salaries rownum greater than

Question

I want to write a query to display employees getting top 3 salaries

  SELECT *
    FROM (SELECT salary, first_name
            FROM employees
        ORDER BY salary desc)
   WHERE rownum <= 3;

But I dont understand how this rownum is calculated for the nested query will this work or if it has problem ,request you to please make me understand:

SELECT *
  FROM (SELECT salary, first_name 
          FROM employees
      ORDER BY salary )
 WHERE rownum >= 3;

I went through this link Oracle/SQL: Why does query "SELECT * FROM records WHERE rownum >= 5 AND rownum <= 10" - return zero rows ,but it again points to a link, which does not gives the answer

What is the structure of your data? And no, I can't **make** you understand anything (nor can anyone else). — , Apr 15 '12 at 05:33

A.B.Cade · Accepted Answer · 2012-04-15T10:08:40.130

a_horse_with_no_name's answer is a good one,
but just to make you understand why you're 1st query works and your 2nd doesn't:

When you use the subquery, Oracle doesn't magically use the rownum of the subquery, it just gets the data ordered so it gives the rownum accordingly, the first row that matches criteria still gets rownum 1 and so on. This is why your 2nd query still returns no rows.

If you want to limit the starting row, you need to keep the subquery's rownum, ie:

SELECT *
FROM (SELECT * , rownum rn
  FROM (SELECT salary, first_name
          FROM employees
      ORDER BY salary ) )sq
WHERE sq.rn >= 3;

But as a_horse_with_no_name said there are better options ...

EDIT: To make things clearer, look at this query:

with t as (
select 'a' aa, 4 sal from dual
union all
select 'b' aa, 1 sal from dual
union all
select 'c' aa, 5 sal from dual
union all
select 'd' aa, 3 sal from dual
union all
select 'e' aa, 2 sal from dual
order by aa
)
select sub.*, rownum main_rn 
  from (select t.*, rownum sub_rn from t order by sal) sub 
 where rownum < 4

note the difference between the sub rownum and the main rownum, see which one is used for criteria

@A.B.Cade...one doubt...when the subquery is fired..it will bring all the data(suppose10 rows) as resultset object with rownum assigned from 1 to 10(in desc order of salary)...............................................and now that we got all of table data....when we fire the external query...with rownum>=3 ,then output should return the result as all other rows than the 3 highest salary rows — lowLatency, Apr 15 '12 at 09:20
No, it's the rownum of the outer query therefor it will not match any row (each one will get rownum 1), this is why in the example I had to keep the subquery's rownum (aliased as rn) — A.B.Cade, Apr 15 '12 at 09:37

score 4 · Answer 2 · answered Apr 15 '12 at 07:47

The "rownum" of a query is assigned before an order by is applied to the result. So the rownumw 42 could wind up being the first row.

Generally speaking you need to use the rownum from the inner query to limit your overall output. This is very well explained in the manual:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#i1006297

I prefer using row_number() instead, because you have much better control over the sorting and additionally it's a standard feature that works on most modern DBMS:

SELECT *
FROM (
  SELECT salary, 
         first_name, 
         row_number() over (order by salary) as rn
  FROM employees
)
WHERE rn <= 3
ORDER BY salary;

You should understand that the derived table in this case is only necessary to be able to apply a condition on the generated rn column. It's not there to avoid the "rownum problem" as the value of row_number() only depends on the order specifiy in the over(...) part (it is independent of any ordering applied to the query itself)

Note this would not return employees that have the same salary and would still fall under the top three. In that case using dense_rank() is probably more approriate.

@a_horse_with_no_name... in the code mentioned above...will the rank() function will also work the same way as row_number() {personally i heard the function row_number for first time} — lowLatency, Apr 15 '12 at 09:09
@jain007: the difference between rank() and row_number() is that rank() will assign the same value for two rows if the salaray is the same, whereas for row_number() two different values will be generated. Just try it out. More information in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#i81407 — , Apr 15 '12 at 14:34

score 0 · Answer 3 · answered May 02 '13 at 05:38

if you want to select the people with the top 3 salaries.. perhaps you should consider using analytics.. something more like

SELECT *
FROM (
    SELECT salary, first_name, dense_rank() over(order by salary desc) sal_rank
    FROM employees 
)
WHERE  sal_rank <= 3

ie ALL people with the 3rd highest(ranked) salary amount(or more)

the advantage of this over using plain rownum is if you have multiple people with the same salary they will all be returned.

score 0 · Answer 4 · answered Aug 26 '13 at 19:17

Easiest way to print 5th highest salary.

SELECT MIN(SALARY) FROM (SELECT SALARY FROM EMPLOYEES ORDER BY DESC) WHERE ROWNUM BETWEEN 1 AND 5

according to same if u want to print 3rd or 4th highest salary then just chage last value.(means instead of 5 use 3 or 4 you will get 3rd or 4th highest salary).

SELECT MIN(SALARY) FROM (SELECT SALARY FROM EMPLOYEES ORDER BY DESC) WHERE ROWNUM BETWEEN 1 AND 4

SELECT MIN(SALARY) FROM (SELECT SALARY FROM EMPLOYEES ORDER BY DESC) WHERE ROWNUM BETWEEN 1 AND 3

score 0 · Answer 5 · answered Aug 30 '13 at 10:36

0

SELECT EMPNO, SAL, (SELECT SUM(E.SAL) FROM TEST E WHERE E.EMPNO <= T.EMPNO) R_SAL FROM (SELECT EMPNO, SAL FROM TEST ORDER BY EMPNO) T

answered Aug 30 '13 at 10:36

sohail

1

score 0 · Answer 6 · edited Sep 20 '13 at 02:37

0

Easiest way to find the top 3 employees in oracle returning all fields details:

SELECT *
FROM (
    SELECT * FROM emp  
      ORDER BY sal DESC)
WHERE rownum <= 3 ;

edited Sep 20 '13 at 02:37

nedaRM

1,837
1
14
28

answered Sep 20 '13 at 02:11

ANAND

1

score 0 · Answer 7 · edited Jan 22 '15 at 09:29

0

select * 
from (
    select emp.*, 
           row_number() over(order by sal desc)r 
    from emp
) 
where r <= 3;

edited Jan 22 '15 at 09:29

DB5

13,553
7
66
71

answered Jan 22 '15 at 08:58

Manikanth Pedapudi

1

Welcome to Stackoverflow. It would be great if you could add some description to go with the SQL, to help others better understand the answer. – DB5 Jan 22 '15 at 09:27

score 0 · Answer 8 · edited Aug 15 '18 at 13:34

0

SELECT Max(Salary)
FROM Employee
WHERE Salary < (SELECT Max(salary) FROM employee WHERE Salary NOT IN (SELECT max(salary) FROM employee))
ORDER BY salary DESC;

edited Aug 15 '18 at 13:34

Baptiste Mille-Mathias

2,144
4
31
37

answered Aug 15 '18 at 12:32

Priya Nimodia

1

If the interviewer told you that without using rownum, limit and ranl you have to find the third highest salary then you can try this – Priya Nimodia Aug 15 '18 at 12:33
Hello and Welcome, Put your comment in the answer you just wrote above instead of adding it in comments. Read [answer] for more detail – Baptiste Mille-Mathias Aug 15 '18 at 12:38

ORACLE sql query for getting top 3 salaries rownum greater than

8 Answers8

Linked