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

Is sql easier than java?

0 Answers  

What is sql mysql pl sql oracle?

0 Answers  

What is the difference between inner join and natural join?

0 Answers  

Give which cursor is better for better performance means type of cursors?

2 Answers  

Why do we need cursor in pl sql?

0 Answers  

What is dcl in sql?

0 Answers  

What is the fastest way of accessing a row in a table?

5 Answers   TCS,

Explain raise_application_error.

0 Answers  

Hi am new to PLSQL & facing problems in writing code like in SP, Functions, so any one having some SP coding with in depth explanation please share with me my Email ID suvarnaatsuvarna@rediffmail.com Or taking tanning on this please do contact me

0 Answers  

How many types of primary keys are there?

0 Answers  

How can you get sql*loader to commit only at the end of the load file? : aql loader

0 Answers  

What is bulk collections?

2 Answers