Sql – ORACLE sql query for getting top 3 salaries rownum greater than

oraclesql

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

Best Answer

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

Related Topic