Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!uunet!fernwood!oracle!news
From: ddruker@.com (Daniel Druker)
Newsgroups: comp.databases
Subject: Re: Duplicated tuples problem (Oracle/SQL)
Keywords: SQL*Plus, OLS, Support
Message-ID: <1991Jan9.070909.15966@oracle.com>
Date: 9 Jan 91 07:09:09 GMT
References: <772@keele.keele.ac.uk> <CIMSHOP!DAVIDM.90Dec21095239@uunet.UU.NET> <12224@sybase.sybase.com> <775@keele.keele.ac.uk> <946@kps.UUCP>
Sender: ddruker@oracle.com (Daniel Druker)
Reply-To: ddruker@oracle.com (Daniel Druker)
Distribution: world, usa, oracle
Organization: Oracle Corporation, Redwood Shores, CA
Lines: 119


I've extracted the following file from Oracle's On-Line Support System.
Among other things, It contains a SQL Statement that will help 
eliminate your duplicate tuples. (I also think the one about finding 
the three highest paid employees is interesting) 

For those of you Oracle Customers out there who have purchased support, 
you can log on to the OLS system yourselves and peruse several hundred 
articles, helpful hints, and release notes at your leisure. All Oracle
supported customers may use this service. Contact your support 
representative for more info.

Regards, 

- Dan

Daniel Druker
Senior Sales Consultant 
Oracle Corporation

------------------
Please insert standard disclaimer here...
------------------

SQL*Plus Tricks
Dana Hausman
VMS Support Group
Oracle Worldwide Support


                           SQL*Plus Tricks
                           ---------------


  Q: How can I pass a parameter to SQL*Plus which contains spaces?

  A: For example, if the SQL script TEST.SQL looks like:

       SELECT '&1'
       FROM dual;

     Then you can use either of the following syntax to pass a value
     which contains spaces as a parameter:

       SQLPLUS scott/tiger @test "'This is a test.'"
       SQLPLUS scott/tiger @test """This is a test"""

     This is a VMS-specific workaround.


  Q: How can I assign unique values to rows in a table?

  A: V5: Create a new column (SEQNO, for example) which is NOT NULL.
         Then use the pseudo-column ROWNUM as follows:

           UPDATE table_name SET seqno = rownum;

     V6:   CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
           UPDATE table_name SET seqno = sequence_name.NEXTVAL;

         Finally, a unique index should be created on this column.


  Q: How can I perform a query like "Find the 3 highest paid
     employees?"

  A: Using the EMP table as an example:

       SELECT ename, sal
       FROM emp A
       WHERE 3 > (SELECT count(*) FROM emp B WHERE B.sal > A.sal)
       ORDER BY sal desc;

     This query selects an employee record if there are less than 3
     employees with higher salaries.


  Q: How can I delete duplicate rows from a table?

  A: There are 2 ways to do this:

       1. CREATE TABLE emp2 AS SELECT distinct * FROM emp;
          DROP TABLE emp;
          RENAME emp2 TO emp;

       2. Step #1 below is necessary in ORACLE v5 because to use the
          pseudo-column ROWID to update or delete a row, the row must
          first be selected for update to obtain the required lock. If
          this step is not performed, in ORACLE v5 you will get the
          error ORA-0061: row updated or deleted by ROWID must first
          be read for update.

            Step #1:

              SELECT empno FROM emp A
              WHERE rowid > (SELECT min(rowid) FROM emp B
                             WHERE B.empno = A.empno)
              FOR UPDATE OF empno;

            Step #2:

              DELETE FROM emp A
              WHERE rowid > (SELECT min(rowid) FROM emp B
                              WHERE B.empno = A.empno);


  Q: How can I have NULL values appear first in v6?

  A: In v5 NULL values were sorted lowest on ascending sequences.
     This behavior has been reversed in v6, but can be changed using
     the NVL command by replacing the NULL value with a very low
     value. For example,

       SELECT ename, sal, comm
       FROM emp
       ORDER BY nvl(comm, -999);

----------------------------------------------------------------------
Document #99246.503      OLS Bulletin Board       Revised: 24-MAY-1990