Select Rownum from Salary Where Rownum=3;

select rownum from salary where rownum=3;

Oracle assigns values to ROWNUM sequentially as rows are produced by the query - thus, the first row fetched gets ROWNUM=1, the second row fetched gets ROWNUM=2, the third row fetched gets ROWNUM=3, etc. Notice - for a row to be assigned ROWNUM=3 two preceding rows MUST be fetched. And this is why your query returns no rows. You're asking the database for the third row fetched - but rows 1 and 2 have never been fetched.

To demonstrate, try running the following queries:

SELECT S.* FROM SALARY S;          -- Should return all rows
SELECT ROWNUM, S.* FROM SALARY S;  -- Should return all rows with ROWNUM prepended
SELECT ROWNUM, S.* FROM SALARY WHERE ROWNUM=3;  -- Should return no rows

To work around your problem, try the following:

SELECT ROW_NUMBER FROM
  (SELECT ROWNUM AS ROW_NUMBER, S.* FROM SALARY S)
  WHERE ROW_NUMBER = 3;

Share and enjoy.

ORACLE sql query for getting top 3 salaries rownum greater than

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

#SQL #QUERY #ROWNUM #ORACLE

In standard SQL, most databases, and Oracle 12C+, you can instead use:

SELECT DISTINCT salary 
FROM employees
ORDER BY salary desc
OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;

Select n'th row using ROWNUM Oracle

In the second query, you are not using rownum but a fixed value: rn. Its values are computed in the subquery.

In the first query, the second rownum is not the same rownum as the rownum in the subselect. The rownum in the subselect works for the rowset in the subselect. And the rownum in the outer query works for the outer query. So, your first query is seen by oracle like:

SELECT SALARY
FROM something
WHERE ROWNUM >= N;
RETURN result;

This won't give records because first row is rownum = 1 and its not >= N. And the second row fetched its now the first and it's not >= N. And so on.

See here another question with the same issue.

If you want, the rownum is the last thing computed. It is a assigned when a row is fetched. So, always the first row has rownum = 1. :)

Row_number function using directly

First, you want dense_rank(), not row_number() if you want the second highest value -- ties might get in the way otherwise.

You can use an arithmetic trick:

SELECT TOP (1) WITH TIES id, salary, depid
FROM emp
ORDER BY ABS(DENSE_RANK() over (PARTITION BY depid ORDER BY salary DESC) - 2)

The "-2" is an arithmetic trick to put the "second" values highest.

That said, I would stick with the subquery because the intent in clearer.

How to use rownum

This will work:

select salary from ( select salary , rownum as rn from (select salary
from e_salary order by salary desc)) where rn = 2;

Why it doesn't work:

When assigning ROWNUM to a row, Oracle starts at 1 and only only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.

Hope u are clear right now.

Selecting the second row of a table using rownum

To explain this behaviour, we need to understand how Oracle processes
ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and
only increments the value when a row is selected; that is, when all
conditions in the WHERE clause are met. Since our condition requires
that ROWNUM is greater than 2, no rows are selected and ROWNUM is
never incremented beyond 1.

The bottom line is that conditions such as the following will work as
expected.

.. WHERE rownum = 1;

.. WHERE rownum <= 10;

While queries with these conditions will always return zero rows.

.. WHERE rownum = 2;

.. WHERE rownum > 10;

Quoted from Understanding Oracle rownum

You should modify you query in this way in order to work:

select empno
from
    (
    select empno, rownum as rn 
    from (
          select empno
          from emp
          order by sal desc
          )
    )
where rn=2;

EDIT: I've corrected the query to get the rownum after the order by sal desc

SQL: SyntaxError: order by with row_number() function

In SQL, the correct comparison operator is =, not ==. So, this is the ANSI SQL version of your query:

with t as (
      select Salary, row_number() over (order by Salary desc) as salary_ord 
      from Employee
     )
select Salary
from t
where salary_ord = 2;

However, your error suggests that your database doesn't support with or window functions.

specific row number with all the fields

rownum = 3 won't work because rownum is applied to the generated resultset. The first row in a result set always has rownum = 1. No resultset can match rownum = 3 so you get no rows back.

The way to do this is with an analytic function like rank(), dense_rank() or row_number().

select * from 
    ( select emp.*
             , dense_rank() over (order by salary desc) rn
      from hr.employees emp
    )
where rn = 3

Whether to use rank(), dense_rank() or row_number() depends on how you want to handle ties.

If you use row_number() you will get the third row in the table sorted by salary descending. Would this be correct if you have two employees earning the highest salary? Or indeed four such lucky employees)?

If you use rank() the subquery will return gaps in the ranking if there are ties, e.g. 1st, 2nd=, 2nd=, 4th, so you won't get any result for third place.

On the other hand dense_rank() doesn't have gaps if there are ties, e.g. 1st, 2nd=, 2nd=, 3rd. That is why I used it above.

---

For the record, the equivalent query using rownum would require an additional nested subquery.

select * from (
    select emp.*
           , rownum as rn
    from 
        ( select * from hr.employees
          order by salary desc) emp
    )
where rn = 3

This has the same outcome as the row_number() analytic solution, that is it ignores ties.

---

Related Topics