0

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;
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Dharshan Sithamparam
  • 361
  • 2
  • 8
  • 18
  • 8
    Please post your SQL as text, not images, and don't post in ALL CAPITALS! – DavidG Nov 10 '15 at 09:42
  • 4
    Without using an `order by`, your `select` statement returns an arbitrary row. A heap organized table is inherently unordered. It would be perfectly valid for Oracle to return any of the three rows. Which row is returned may change over time for a variety of reasons. – Justin Cave Nov 10 '15 at 09:49
  • @JustinCave is spot on, and the conclusion is that it's not sensible to delete based on `rownum`, why do you need to? It's actually not a bad question! – mjsqu Nov 10 '15 at 09:58
  • `delete from testring where rownum=2;` 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. – Lalit Kumar B Nov 10 '15 at 10:41
  • Your problem with rownum have an explanation [here](http://stackoverflow.com/q/9240192/319875) – Florin Ghita Nov 10 '15 at 11:02

4 Answers4

5
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.
Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
2

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);
Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
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';

Giggs
  • 95
  • 4
0

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.

jatin Goyal
  • 85
  • 6