SQL question from Mattgreenspun.com : LUSENET : WebDevelopers : One Thread
From: "SCHROEDER, NATHAN E [FND/1000]"
Save Address To: "'firstname.lastname@example.org'" Subject: RE: STLWEBDEV: Here's a slightly tougher SQL question... Date: Tue, 20 Mar 2001 12:35:45 -0600 Reply-To: email@example.com
-------------------------------------------------------------------------------- And to make that more general:
DELETE FROM table-name A WHERE ROWNUM < (SELECT MAX(ROWNUM) FROM table-name B WHERE A.NAME = B.NAME)
This will also go slowly on a big table. It has to step through every row of the table, and *for each row* it has to do a second scan to find all the rows where NAME matches. If NAME is indexed, the second scan will be pretty fast.
Note that any of these plans will essentially save a random one of the rows with Name="Matt"; if the different rows have different values in other columns, you'll keep who-knows-which-one and lose the others.
ROWNUM is an Oracle extension to standard SQL. If you're using a database other than Oracle, ROWNUM may not work. You should be able to replace it with anything that is unique to each row, such as the primary key of the table.
Nate Schroeder firstname.lastname@example.org
-----Original Message----- From: email@example.com [mailto:firstname.lastname@example.org] Sent: Tuesday, March 20, 2001 9:00 AM Cc: email@example.com; firstname.lastname@example.org Subject: RE: STLWEBDEV: Here's a slightly tougher SQL question...
You can do it in one shot...
For example, if you have table EMPLOYEE (EMP_ID char(4)) which is holding 5 records with EMP_ID value '1' and you want to get rid of 4 records and keep one record..You can execute the following query to get rid of four and retain one.
DELETE FROM EMPLOYEE WHERE EMP_ID = '1' AND ROWNUM < (SELECT MAX(ROWNUM) from EMPLOYEE WHERE EMP_ID='1')
Hope this helps.
On Tue, 20 March 2001, "Shoemaker, Michael (N-MAXIM Group)" wrote:
> -> -----Original Message----- > -> From: Matt Kaatman [mailto:email@example.com] > -> Sent: Monday, March 19, 2001 6:08 PM > -> To: firstname.lastname@example.org > -> Subject: STLWEBDEV: Here's a slightly tougher SQL question... > -> > -> > -> Is there a query I can do to delete all but one of any > -> records that have > -> duplicate entries for a particular field? > -> > -> So let's say I have 5 records with the value of "Matt" for > -> the field Name, I > -> want to delete 4 of those. > -> > -> I salute the person who can do this! I don't think it's > -> possible to do in a > -> single query. I have only figured out manual solutions that > -> require a few > -> steps. If it's possible to do it all in one query that would > -> be awesome! > -> > -> Thanks! > -> Matt Kaatman > -> ThoughtProcess.net > -> http://www.thoughtprocess.net > ->
-- Anonymous, March 20, 2001