Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778

    Unanswered: 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 Smith
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: rownum w/ NOT IN

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

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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:

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •