Geek asks and answers

ORACLE sql query to get top 3 rownum salaries more than

I want to write a request to display employees receiving the top 3 salaries

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

But I don’t understand how this rownum is calculated for a sub-query whether it will work, or if it has problems, please ask me to understand:

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

I went through this Oracle / SQL link : why the query "SELECT * FROM writes WHERE rownum> = 5 And rownum <= 10" - return zero rows , but it again points to a link that does not give an answer

+3
source share
7 answers

a_horse_with_no_name the answer is good,
but just so that you understand why you are doing the 1st request and your second is not:

, Oracle rownum , , rownum , , , - rownum 1 . - .

, rownum, :

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

a_horse_with_no_name , ...

EDIT: , :

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

sub rownum rownum, .,

+6

"rownum" . , rownumw 42 .

, rownum , . :

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

row_number(), , , :

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

, rn. " rownum", row_number() , over(...) ( , )

, , , . dense_rank(), , .

+4

3 . , .. -

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

.. 3- () ( )

rownum - , .

0

5- .

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

, , . ( 5 3 4, 3 4 ).

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
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

0

oracle, :

SELECT *
FROM (
    SELECT * FROM emp  
      ORDER BY sal DESC)
WHERE rownum <= 3 ;
0
select * 
from (
    select emp.*, 
           row_number() over(order by sal desc)r 
    from emp
) 
where r <= 3;
0
source

More articles:


All Articles