5

The database type is PostGres 8.3.

If I wrote: 

SELECT field1, field2, field3, count(*) FROM table1 GROUP BY field1, field2, field3 having count(*) > 1;

I have some rows that have a count over 1. How can I take out the duplicate (I do still want 1 row for each of them instead of +1 row... I do not want to delete them all.)

Example:

1-2-3 1-2-3 1-2-3 2-3-4 4-5-6

Should become :

1-2-3 2-3-4 4-5-6

The only answer I found is there but I am wondering if I could do it without hash column.

Warning I do not have a PK with an unique number so I can't use the technique of min(...). The PK is the 3 fields.

Improve this question
2
  • I am not sure whether I understand it right. You say "The PK is the 3 fields" - then how come you have duplicates in the table like multiple 1-2-3 records. Correct me if I am wrong. Commented Oct 28, 2008 at 15:02
  • The PK was in the 3 fields, we had to remove them for a merge (long story) now we need to put it back. We have some duplicate that we would like to take off. Commented Oct 28, 2008 at 15:10

7 Answers 7

Reset to default
6

This is one of many reasons that all tables should have a primary key (not necessarily an ID number or IDENTITY, but a combination of one or more columns that uniquely identifies a row and which has its uniqueness enforced in the database).

Your best bet is something like this:

SELECT field1, field2, field3, count(*) INTO temp_table1 FROM table1 GROUP BY field1, field2, field3 having count(*) > 1 DELETE T1 FROM table1 T1 INNER JOIN (SELECT field1, field2, field3 FROM table1 GROUP BY field1, field2, field3 having count(*) > 1) SQ ON SQ.field1 = T1.field1 AND SQ.field2 = T1.field2 AND SQ.field3 = T1.field3 INSERT INTO table1 (field1, field2, field3) SELECT field1, field2, field3 FROM temp_table1 DROP TABLE temp_table1
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I am currently trying your suggestion. +1
It works thx, I have modified few thing like DELETE T1 doesnt work, I had to put the T1 after FROM table1 as T1. Few things like that. But the solution was fast and successful here.
I'm glad that it worked for you. Thanks for pointing out my overlooking the T1 alias. I've corrected it in the script.
0

One possible answer is:

CREATE <temporary table> (<correct structure for table being cleaned>); BEGIN WORK; -- if needed INSERT INTO <temporary table> SELECT DISTINCT * FROM <source table>; DELETE FROM <source table> INSERT INTO <source table> SELECT * FROM <temporary table>; COMMIT WORK; -- needed DROP <temporary table>;

I'm not sure whether the 'work' is needed on transaction statements, nor whether the explicit BEGIN is necessary in PostgreSQL. But the concept applies to any DBMS.

The only thing to beware of is referential constraints and in particular triggered delete operations. If those exist, this may prove less satisfactory.

Improve this answer

Comments

0

This will use the OID Object ID (if the table was created with it):

DELETE FROM table1 WHERE OID NOT IN (SELECT MIN (OID) FROM table1 GROUP BY field1, field2, field3)
Improve this answer

Comments

0

Well I should misunderstand something but I'll say :

SELECT DISTINCT field1, field2, field3 FROM table1

Too easy to be good? ^^

Improve this answer

1 Comment

I think Daok wants to delete all but one of the duplicate entries.
0

This is the simplest method I've found:

Postgre SQL syntax:

CREATE TABLE tmp AS SELECT distinct * FROM table1 truncate table table1 insert into table1 select * from tmp drop table tmp

T-SQL syntax:

select distinct * into #tmp from table1 truncate table table1 insert into table1 select * from #tmp drop table #tmp
Improve this answer

Comments

0

A good Answer for this problem, but for SQL Server. It uses the ROWCOUNT that SQL Server offers, to good effect. I have never used PostgreSQL and hence don't know the equivalent of ROWCOUNT in PostgreSQL.

Improve this answer

Comments

0

Using TSQL, no idea if Postgres supports temp tables but you could select into a temp table, and then loop through and delete and insert your results back into the original

-- **Disclaimer** using TSQL -- You could select your records into a temp table with a pk Create Table #dupes ([id] int not null identity(1,1), f1 int, f2 int, f3 int) Insert Into #dupes (f1,f2,f3) values (1,2,3) Insert Into #dupes (f1,f2,f3) values (1,2,3) Insert Into #dupes (f1,f2,f3) values (1,2,3) Insert Into #dupes (f1,f2,f3) values (2,3,4) Insert Into #dupes (f1,f2,f3) values (4,5,6) Insert Into #dupes (f1,f2,f3) values (4,5,6) Insert Into #dupes (f1,f2,f3) values (4,5,6) Insert Into #dupes (f1,f2,f3) values (7,8,9) Select f1,f2,f3 From #dupes Declare @rowCount int Declare @counter int Set @counter = 1 Set @rowCount = (Select Count([id]) from #dupes) while (@counter < @rowCount + 1) Begin Delete From #dupes Where [Id] <> (Select [id] From #dupes where [id]=@counter) and ( [f1] = (Select [f1] from #dupes where [id]=@counter) and [f2] = (Select [f2] from #dupes where [id]=@counter) and [f3] = (Select [f3] from #dupes where [id]=@counter) ) Set @counter = @counter + 1 End Select f1,f2,f3 From #dupes -- You could take these results and pump them back into --your original table Drop Table #dupes

Tested this on MS SQL Server 2000. Not familiar with Postgres' options but maybe this will lead you in a right direction.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.