rownum=5 returns no data but table has 10 rows

Question

I have a table emp (ename,eid,did,sal,sex) where column did is foreign key with another table dept (did,dname).

I want to get the nth sal of the company along with person's details . I am executing following query(my table has 10 rows)

    SELECT rownum,vi.* FROM
   (SELECT * FROM emp ORDER BY sal desc) vi
   WHERE ROWNUM >5;

when I execute rownum>5 or rownum=5 get "no data" but if i execute rownum<5 then i get 4 rows displayed.

.I want the data when I do rownum=n

It is not working because: for the first row assumes the `ROWNUM` of 1 and since your `WHERE` clause is `ROWNUM>5` then this reduces to `1>5` and the row is discarded. The subsequent row will then be tested against a `ROWNUM` of 1 (since the previous row is no longer in the output and now does not require a row number), which will again fail the test and be discarded. Repeat, ad nauseum and all rows fail the `WHERE` clause filter and are discarded. You need 3 levels of nested queries - the inner one orders, the middle assigns the `ROWNUM` pseudo-coulmn and the outer filters on it. — MT0, Apr 12 '17 at 09:06

score 0 · Answer 1 · answered Apr 12 '17 at 09:03

0

Can you try this?

SELECT * FROM 
(SELECT ROWNUM AS X, vi.* 
  FROM  (SELECT * FROM emp ORDER BY sal desc) vi
 ) B 
WHERE X>5
;

answered Apr 12 '17 at 09:03

etsa

4,805
1
5
18

score 0 · Answer 2 · answered Apr 12 '17 at 09:04

0

see this article On ROWNUM and Limiting Results By Tom Kyte

in your sample you can use row_number function

select * from (
select row_number() over (order by col1 desc) rn, * from test_table 
)q where rn = 5

answered Apr 12 '17 at 09:04

are

2,425
2
19
26

score 0 · Answer 3 · answered Apr 12 '17 at 09:05

Assuming you want only 5th row, you can try this:

select *
from (
    select rownum as rn,
        vi.*
    from (
        select *
        from emp
        order by sal desc
        ) vi
    where ROWNUM <= 5
    ) t
where rn = 5;

or using window functions:

select *
from (
    select row_number() over (order by sal desc) as rn,
        vi.*
    from emp vi
    ) t
where rn = 5;

If you are using 12c+, you can use top n queries:

select *
from emp
order by sal desc
offset 4 rows
fetch first 1 row only;

rownum=5 returns no data but table has 10 rows

3 Answers3