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 pivot table in sql?

0 Answers  

How will you debug your procedure? If your procedure is around 2000 lines and the expected output is 10 and we get only output 5.So how will you debug it? Somebody pls give the correct answer?

2 Answers   Fidelity,

How do I view a sql database?

0 Answers  

which tcp/ip port does sql server run on? : Sql dba

0 Answers  

What is a left inner join?

0 Answers  

Are null values same as that of zero or a blank space?

0 Answers  

what is 'mysqld'? : Sql dba

0 Answers  

What is the use of index in hive?

0 Answers  

How can I get the number of records affected by a stored procedure?

0 Answers  

How do I turn a list into a table?

0 Answers  

what are date and time data types in mysql? : Sql dba

0 Answers  

suppose we have values like 1 5 7 in a colum.Now we want numbers like(2 3 4 6) that exists between 1 5 7.How can we do this using sql query??

5 Answers   Keane India Ltd,