Re: Select statement question

From: Jeff Kish <jeff.kish_at_ait-mmii.com>
Date: Tue, 02 Oct 2001 11:54:29 -0400
Message-ID: <8lojrt0vtje6606ov35fr1r93tt7r6v8ca@4ax.com>

Wait, I found it on some other Oracle support web page. At: http://www.onwe.co.za/frank/faqlink.htm I searched for the word duplicate and got this entry: 2. deleting duplicate rows

                   deleting duplicate rows [ Replies ] [ Post a Reply
] [ Message Index ] Posted by Ranjitha
                 (202.9.170.129) on June 09, 2001 at 01:11:45:...
                 URL:

http://www.orafaq.com/msgboard/sql/messages/6023.htm Score: 95% Size: 4 kB

The actual reply was:

delete from emp t1 where rowid <>
 (select max(rowid) from emp t2
 where t1.empid = t2.empid

 and t1.ename = t2.ename
 .....................
 ....................);

 would delete the duplicate rows..

 Bala.

Thanks
Jeff
On Tue, 02 Oct 2001 10:23:55 -0400, Jeff Kish <jeff.kish_at_ait-mmii.com> wrote:

>Say,
>
>does anyone know of a good sql way (or do I need pl/sql?) to
>clean up such duplicate rows so I could instantiate a primary key if I
>wanted to?
>
>Thanks
>Jeff
>
>
>On 18 Sep 2001 02:48:49 -0700, tony1979_at_hotmail.com (Tony) wrote:
>
>>Hi all, I'm relatively new to SQL, and was wondering if anybody could
>>help me with the following:
>>
>>In a select statement, if Oracle has more than 1 row that is a
>>duplicate, I only want to display it once ( I'm not using primary
>>keys, because nothing is neccessarily unique ).
>>
>>For example, a table which holds the following data:
>>
>>SELECT Id, Site, Equipment FROM table1 order by Id asc;
>>
>>ID SITE EQUIPMENT
>>01 A1 switch
>>01 A1 switch
>>02 B1 switch
>>
>>Should only return row 1 and 3 ( or 2 and 3 ).
>>
>>Is this possible?
>>
>>Thanks in advance,
>>
>>Tony Lee, UK.
Received on Tue Oct 02 2001 - 10:54:29 CDT