I have some values as below:
MYSTRING
--------
Dharshan
Ramalingam
Devid
I can select the specific row value through below query
select * from ( select mystring , rownum rn FROM teststring )
where rn = 2;
tell the way to delete this second row giving the row no and give me the brief explanation . I have tried as below but its not work....
delete from testring where rownum=2;
select * from ( select mystring , rownum rn FROM teststring ) where rn = 2;
Your query returns rows randomly without a specified order. A heap-organized table is a table with rows stored in no particular order. This is a standard Oracle table.
From Oracle documentation,
Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
delete from testring where rownum=2;
Your above query shows you do not understand how ROWNUM works.
Your query will never delete any rows.
A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:
select *
from t
where ROWNUM = 2;
Because ROWNUM = 2
is not true for the first row, ROWNUM
does not advance to 2. Hence, no ROWNUM
value ever gets to be greater than 1.
How to correctly use ROWNUM:
As you wanted to select a row based on ROWNUM, you could do something like pagination:
SQL> SELECT empno
2 FROM
3 (SELECT empno, sal, ROWNUM AS rnum FROM
4 ( SELECT empno, sal FROM emp ORDER BY sal
5 )
6 )
7 WHERE rnum =2;
EMPNO
----------
7900
It happens in three levels:
- Innermost sub-query first sorts the rows based on the ORDER BY clause.
- In second level, the sub-query assigns ROWNUM
- The outermost query filters the rows based on the ROWNUM given by inner query which is no more a pseudo-column but the sub-query resultset.
if you want to delete all rows except one(just for understanding how the rownum works):
delete
from table
where rowid in (select rwid
from (select rownum as rn, rwid
from( select rowid as rwid from table)
order by rwid)
where rn > 1);
or, more simpler:
delete from table
where rowid <> (select rowid from table where rownum = 1);
Rownum is a pseudocolumn.
The rownum is not assigned permanently to the row. It means you can't build your query based on this criteria.
You should instead delete your row with
delete from teststring where mystring = 'RAMALINGAM';
I have tried this Query and it's working fine.
DELETE FROM NG_USR_0_CLIENT_GRID_NEW WHERE rowid IN
( SELECT rowid FROM
(
SELECT wi_name, relationship, ROW_NUMBER() OVER (ORDER BY rowid DESC) RN
FROM NG_USR_0_CLIENT_GRID_NEW
WHERE wi_name = 'NB-0000001385-Process'
)
WHERE RN=2
);
Please suggest that can we improve it's performance.
Any comment is appreciated.