Oracle-L: RE: SQL puzzle - using where (a, b, c) in select (a, b, c from...

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 19 Nov 2002 12:03:47 -0800
Message-ID: <F001.00507165.20021119120347@fatcity.com>

Thank you for your answer.
This is not an answer to the problem as stated in the original description. Your statement deleted duplicate rows in widgets_copy even if they were NOT present in widgets.

-----Original Message-----
Sent: lundi, 18. novembre 2002 22:44
To: Multiple recipients of list ORACLE-L

SQL> ed
Wrote file afiedt.buf

  1 select * from widgets_copy
  2* order by 1,2,3
SQL> /         ID COST SELL
---------- ---------- ----------

         1         10         20
         1         10         20
         1         10         30
         1         10         30
         1         10
         1         10
         1
         1

8 rows selected.

SQL> delete widgets_copy where rowid not in (select min(rowid) 2 from widgets_copy group by id, cost,sell);

4 rows deleted.

SQL> select * from widgets_copy
2 order by 1,2,3
3 /

ID COST SELL
---------- ---------- ----------

         1         10         20
         1         10         30
         1         10
         1

SQL> HTH,
Rgds,
Nirmal.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Nov 19 2002 - 14:03:47 CST