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 autonomouse transaction?

3 Answers   Oracle,

What is clustered index in sql?

0 Answers  

What is on delete set null?

0 Answers  

Why do we need pl sql?

0 Answers  

What is a unique constraint?

0 Answers  

What are the different sql commands?

0 Answers  

Can we connect to postgresql using sql developer?

0 Answers  

There are 5 records in a table and we have implemented two triggers that are :pre_query and post_query how many times these triggers will fire.

2 Answers   Maruti Suzuki,

i hv 30 rows with date.ex:1month hav 4 weeks i want 1st day of the every week.write the qry for that.example jan has 4 weeks i need 1st dd for evry wk

1 Answers   Aspire,

What is a sql schema used for?

0 Answers  

Is sql a backend language?

0 Answers  

What is write ahead logging in sql server?

0 Answers