rownum w/ NOT IN

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 SmithI 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:

-- Select records
ROWNUM = 0
loop
Get next row
If row matches WHERE clause then
ROWNUM = ROWNUM+1
output(ROWNUM) = this row
end if
end loop