DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Duplicate Rows

  1. #1
    Join Date
    Feb 2003
    Location
    Pune,maharashtra,India
    Posts
    4

    Duplicate Rows

    Hi,

    I want to delete duplicate rows from a table using rownum.

    If I use this command

    Delete from mytable where rownum > 1 (to delete all rows except rownum = 1) nothing is getting deleted.(There are 2 similar rows in mytable)

    But If I use following command

    Delete from mytable where rownum < 2 then row with rownum = 1 is getting deleted.

    A bit confused why the first sql is not working while the second sql is working fine.

    Regards
    Sudhakar

  2. #2
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    while using rownum in DML statement '>' is invalid, u have to use only '<' less than operator

    Only this SQL query works

    Delete from mytable where rownum < 2 then row with rownum = 1 is getting deleted

  3. #3
    Join Date
    Feb 2003
    Location
    Pune,maharashtra,India
    Posts
    4
    Hello Sir,

    My confusion is if one operator '<' is working then why not '>'..??How come use of '>' is invalid whereas it is valid to use '<'.Can I get some more information/reding references for the same?

    Regards

  4. #4
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    The rownum has one side effect. You cannot select a row number that is greater than a particular number. For example, if you want all but the first two rows and you execute the following statement:

    SELECT ROWNUM, Empno, ename
    FROM emp
    WHERE ROWNUM > 2;
    you receive the following message:

    no rows selected

    The reason is that when the first row is returned, it is given the ROWNUM value of 1. It is tested against the WHERE clause and then rejected. The next row is then given the ROWNUM value of 1. It is tested and also rejected. This continues until no more rows are in the table, and no rows are returned in the result set.

  5. #5
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    ROWNUM is a psuedocolumn , the value is generated when you select the records, therefore you cannot get values for the> [ greater than ]clause.

    regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    In relational database you cannot use rownum to eliminate duplicates..You should be using rowids...

    regards
    Hrishy

  7. #7
    Join Date
    Feb 2003
    Location
    Pune,maharashtra,India
    Posts
    4
    Hi All,

    Thanks for your help.The clarifications/suggestions are very useful to clear my doubts.

    Regards

    Sudhakar

  8. #8
    Join Date
    Feb 2003
    Posts
    1
    Hi Sudhakar,

    See this is how u can delete duplicate rows from the table:

    To delete duplicate rows from this table, use the following query:
    delete from empl a where rowid > (
    select min(rowid) from empl b where
    a.desc = b.desc and
    a.emplid = b.emplid);


    Vineet

Posting Permissions

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


Click Here to Expand Forum to Full Width