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

How the execution will be done in exceptions?

2 Answers  

What is sql rowcount?

0 Answers  

How is indexing done in search engines?

0 Answers  

table name :Tab fields name 1.trx_no (pk) 2.trx_date 3.account code (7 char) 4.account type (1 char) 5.amt Tab contains account code day wise debit and credit transaction , account type fiels can have 2 value D for debit and c for Credit . write a query to display the account code wise total debit and credit bal for the month of april 2004. write a query to display account code wise new amt credit for the april 2004

2 Answers   Maruti Suzuki,

what is data manipulation language? : Sql dba

0 Answers  

Which one of the following join types will always create a Cartesian Product? 1. CROSS JOIN 2. LEFT OUTER JOIN 3. RIGHT OUTER JOIN 4. FULL OUTER JOIN 5. INNER JOIN

2 Answers  

Hi Guys, I have a situation where I need to access the column values from rowtype variable. However, the column names are dynamic. below is sample code: declare Cursor c1 is select * from emp; Cursor c2 is select column_name from xyztable; v_c2 c2%rowtype; v_str varchar2 v_value varchar2(200); begin for rec in c1 loop open c2;---this cursor has column names like EMPLOYEE_ID, FIRST_NAME, LAST_NAME etc. loop fetch c2 into v_c2; exit when c2%notfound; /* now lets say i want to access value of LAST_NAME from cursor c1, so I am writing below code, however it does not work as expected */ v_str:= 'rec.'|| v_c2.column_name; -- this will give me string like "rec.EMPLOYEE_ID" v_value:=v_str; end loop; end loop; end; / Plz help ASAP.Thanks.

2 Answers  

What is a column in a table?

0 Answers  

Easy way to convert tableau "IF - ELSEIF" statements to Netezza "CASE" statements.

1 Answers   CTS,

What does the acronym acid stand for in database management?

0 Answers  

what is self join and why is it required? : Sql dba

1 Answers  

Why does sql need a server?

0 Answers