Answer / alok narayan

select * from (select * from emp order by rowid desc )
where rownum <= 3 order by rowid;

Answer / mah6326955

SELECT empno
FROM (SELECT ROWNUM r, emp_code
FROM emp
ORDER BY empno DESC) t1
WHERE t1.r <= 3

Answer / sunil bisht

Hi Prakash,

You did one mistake in this query when you are use rownum
is always start fetch the record from very first record
from table
Row num can work only with the follow using operators

=(only with 1)(select * from emp where rownum=1;) if you
are use 2 or other number in place of 1 so result is no
data found;

<(any vlaue)(select * from emp where rownum<12 if you are
using the > sign in place of < sign so result is no data
found

<=(any value)

Between 1 and any value

>=(only with 1)

<>(any value) this will return all records less than the
given value


if you want to retrive last three records from table then
you can used

select * from emp where rownum<=(select count(*) from emp)
minus select *from emp where rownum <=(select count(*)-3
from emp)

you get the last three record from the table

other method is

select * from emp e where 3>(select count(*) from emp d
where d.rowid>e.rowid);

Answer / gnreddy

try this one.
select * from emp where 3 >( select count(1) from emp e where e.rowid > emp.rowid);

Answer / pavithra

select * from emp where rowid in(select rowid from emp
where rownum<=&upto minus select rowid from emp where
rownum<&start)

Answer / alok narayan

Rownum assigned at the runtime on the basis of rownum you
not fetch last three records.

for no records output: Rownum always use <= or >= , if use
rownum < or > its giving no output.

Answer / ramareddy

u don't use minum operator to count(*) in subquerry

Answer / ammu

Hi Prakash Kumar,

this is the problem with rownum. In general never use = or
> with rownum. I will explain the reason.

Siuppose u hav 2 rowns in a table.

when u say select * from emp where rownum =2.

then 2 row will get selected ...but as soon as it gets
selected..since it is the only row in the selection its row
num will change to 1.

so this will return 0 rows.

similarly..

when u say rownum >1
2 row will get selected but as soon as it get selected its
rownum changes to 1.. so no rows selected again

Answer / bikash khuntia

select * from (
select rownum rw,bk.sal from
(select sal from TEMP_SAL order by rowid desc) bk) bik
where bik.rw<=3

Answer / arvind kumar

select * from employees minus select * from employees where
rownum < (select count(*)-2 from employees);

What is the purpose of the partition table?

0 Answers  

how to insert the data through views? The view is depending upon more than two tables? how to update materalized views?

3 Answers   3i Infotech,

I have 2 Databases. How can create a table in particular database? How can i know the list of tables presented each database?( in oracle 10g)

5 Answers   Relq,

What are some predefined exceptions in pl/sql?

0 Answers  

how are mysql timestamps seen to a user? : Sql dba

0 Answers  

how many columns can be used for creating index? : Sql dba

0 Answers  

what are wild cards used in database for pattern matching ? : Sql dba

0 Answers  

What is difference between hql and sql?

0 Answers  

what is ref cursor in pl/sql?

3 Answers  

Why use truncate instead of delete?

0 Answers  

What is integrity in sql?

0 Answers  

How many columns should be in an index?

0 Answers