Hi,

SQL> select fname, lname, rownum
2 from sample;

FNAME LNAME ROWNUM
---------- ---------- ----------
John Smith 1
John Smith 2

I wish to delete one of the above rows. Could someone please tell me what is happening below

SQL> select *
2 from sample
3 where rownum IN
4 (select rownum
5 from sample);

FNAME LNAME
---------- ----------
John Smith
John Smith

SQL> select *
2 from sample
3 where rownum NOT IN
4 (select rownum
5 from sample);

FNAME LNAME
---------- ----------
John Smith
John Smith

I can't explain what is happening with your queries exactly, but it is due to the fact that ROWNUM does not behave as you expect.

ROWNUM is a tricky beast, as it is assigned to the records as they are selected. For example, this will NEVER return a row:

select * from mytable where rownum > 1;

It gets the first row from mytable, assigns rownum=1, then checks the condition rownum > 1, which is false.
It gets the second row, assigns rownum = 1 again (since no previous row has been selected yet), then checks the condition rownum > 1, which is false.
etc. etc. etc.

When you have duplicate records, the only way to distinguish them is by the ROWID, which is a physical address:

SQL> select fname, lname, rowid
2 from sample;

FNAME LNAME ROWID
---------- ---------- ----------
John Smith AAA6BDAAFAAABIPAAA
John Smith AAA6BDAAFAAABIPAAB

Now you can:

delete sample where rowid='AAA6BDAAFAAABIPAAB';

I'm sorry could you please explain the logic here, how is the second row also assigned 1 which leads to the result at the end of n rows as being
1
2
.
n

It gets the first row from mytable, assigns rownum=1, then checks the condition rownum > 1, which is false.
It gets the second row, assigns rownum = 1 again (since no previous row has been selected yet), then checks the condition rownum > 1, which is false.

I appreciate your comment as my problem is solved, just would like to understand rownum.

Cheers.

ROWNUM applies to the output of the selection process, not the input. So for example if you select any 5 records from a table, they will always have ROWNUM values from 1 to 5, in the order the records were found. If the query has an ORDER BY clause, this is applied after the ROWNUMs have been assigned, hence:

SQL> select dname, rownum from dept;

DNAME ROWNUM
-------------- ----------
ACCOUNTING 1
RESEARCH 2
SALES 3
OPERATIONS 4

SQL> select dname, rownum from dept order by dname;

DNAME ROWNUM
-------------- ----------
ACCOUNTING 1
OPERATIONS 4
RESEARCH 2
SALES 3

SQL> select dname, rownum from dept where dname='SALES';

DNAME ROWNUM
-------------- ----------
SALES 1

SQL> select dname, rownum from dept where dname='ACCOUNTING';

DNAME ROWNUM
-------------- ----------
ACCOUNTING 1

See? If you think of the query processor as a program it looks like this: