pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete duplicates
10+ messages / 9 participants
[nested] [flat]
* Delete duplicates
@ 2003-06-22 09:15 Rudi Starcevic <rudi@oasis.net.au>
2003-06-22 10:20 ` Re: Delete duplicates Ian Barwick <barwick@gmx.net>
2003-06-22 11:03 ` Re: Delete duplicates Paul Thomas <paul@tmsl.demon.co.uk>
2003-06-22 18:33 ` Re: Delete duplicates Germán Gutiérrez <germangutierrezg@entelchile.net>
2003-06-22 21:17 ` Re: Delete duplicates Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 4 replies; 10+ messages in thread
From: Rudi Starcevic @ 2003-06-22 09:15 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a table with duplicates and trouble with my SQL.
I'd like to keep a single record and remove older duplicates.
For example below of the 6 recods I'd like to keep records
4 and 6.
TABLE: aap
id | keyword
----+-----------------
1 | LEAGUE PANTHERS
2 | LEAGUE PANTHERS
3 | LEAGUE PANTHERS
4 | LEAGUE PANTHERS
5 | LEAGUE BRONCOS
6 | LEAGUE BRONCOS
Here is my SQL so far, it will select records 1 to 5 instead
of 1,2,3 and 5 only.
Any help greatly appreciated. I think I need a Group By somewhere in there.
select a1.id
from aap a1
where id < ( SELECT max(id) FROM aap AS a2 )
AND EXISTS
(
SELECT *
FROM aap AS a2
WHERE a1.keyword = a2.keyword
)
Regards
Rudi.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete duplicates
2003-06-22 09:15 Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
@ 2003-06-22 10:20 ` Ian Barwick <barwick@gmx.net>
3 siblings, 0 replies; 10+ messages in thread
From: Ian Barwick @ 2003-06-22 10:20 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
On Sunday 22 June 2003 11:15, Rudi Starcevic wrote:
> Hi,
>
> I have a table with duplicates and trouble with my SQL.
(...)
> select a1.id
> from aap a1
> where id < ( SELECT max(id) FROM aap AS a2 )
> AND EXISTS
> (
> SELECT *
> FROM aap AS a2
> WHERE a1.keyword = a2.keyword
> )
How about (untested):
SELECT a1.id
FROM aap a1
WHERE id = (SELECT MAX(id) FROM aap a2
WHERE a2.keyword = a1.keyword)
Ian Barwick
barwick@gmx.net
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete duplicates
2003-06-22 09:15 Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
@ 2003-06-22 11:03 ` Paul Thomas <paul@tmsl.demon.co.uk>
3 siblings, 0 replies; 10+ messages in thread
From: Paul Thomas @ 2003-06-22 11:03 UTC (permalink / raw)
To: pgsql-sql
On 22/06/2003 10:15 Rudi Starcevic wrote:
>
>
> Hi,
>
> I have a table with duplicates and trouble with my SQL.
> I'd like to keep a single record and remove older duplicates.
> For example below of the 6 recods I'd like to keep records
> 4 and 6.
>
> TABLE: aap
> id | keyword
> ----+-----------------
> 1 | LEAGUE PANTHERS
> 2 | LEAGUE PANTHERS
> 3 | LEAGUE PANTHERS
> 4 | LEAGUE PANTHERS
> 5 | LEAGUE BRONCOS
> 6 | LEAGUE BRONCOS
>
> Here is my SQL so far, it will select records 1 to 5 instead
> of 1,2,3 and 5 only.
>
> Any help greatly appreciated. I think I need a Group By somewhere in
> there.
>
> select a1.id
> from aap a1
> where id < ( SELECT max(id) FROM aap AS a2 )
> AND EXISTS
> (
> SELECT *
> FROM aap AS a2
> WHERE a1.keyword = a2.keyword
> )
I just tries this with 7.3.3:
select max(id), keyword from aap where keyword in (select distinct keyword
from aap) group by keyword;
max | keyword
-----------------------
6 | LEAGUE BRONCOS
4 | LEAGUE PANTHERS
(2 rows)
HTH
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete duplicates
2003-06-22 09:15 Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
@ 2003-06-22 18:33 ` Germán Gutiérrez <germangutierrezg@entelchile.net>
3 siblings, 0 replies; 10+ messages in thread
From: Germán Gutiérrez @ 2003-06-22 18:33 UTC (permalink / raw)
To: pgsql-sql
Hi,
you need find duplicates and then you remove them
delete from aap where id not in ( select max(id) from aap b where
aap.keyword = b.keyword );
Germán
Sorry about my english
-----Mensaje original-----
De: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] En nombre de Rudi Starcevic
Enviado el: Domingo, 22 de Junio de 2003 5:15
Para: pgsql-sql@postgresql.org
Asunto: [SQL] Delete duplicates
Hi,
I have a table with duplicates and trouble with my SQL.
I'd like to keep a single record and remove older duplicates.
For example below of the 6 recods I'd like to keep records
4 and 6.
TABLE: aap
id | keyword
----+-----------------
1 | LEAGUE PANTHERS
2 | LEAGUE PANTHERS
3 | LEAGUE PANTHERS
4 | LEAGUE PANTHERS
5 | LEAGUE BRONCOS
6 | LEAGUE BRONCOS
Here is my SQL so far, it will select records 1 to 5 instead
of 1,2,3 and 5 only.
Any help greatly appreciated. I think I need a Group By somewhere in
there.
select a1.id
from aap a1
where id < ( SELECT max(id) FROM aap AS a2 )
AND EXISTS
(
SELECT *
FROM aap AS a2
WHERE a1.keyword = a2.keyword
)
Regards
Rudi.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.1K) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete duplicates
2003-06-22 09:15 Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
@ 2003-06-22 21:17 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-06-23 05:16 ` Re: Delete duplicates Denis Arh <denis@exonium.net>
3 siblings, 1 reply; 10+ messages in thread
From: Franco Bruno Borghesi @ 2003-06-22 21:17 UTC (permalink / raw)
To: rudi@oasis.net.au; +Cc: pgsql-sql
try this
DELETE FROM aap WHERE id NOT IN (
SELECT max(id)
FROM aap
GROUP BY keyword
);
>
>
> Hi,
>
> I have a table with duplicates and trouble with my SQL.
> I'd like to keep a single record and remove older duplicates.
> For example below of the 6 recods I'd like to keep records
> 4 and 6.
>
> TABLE: aap
> id | keyword
> ----+-----------------
> 1 | LEAGUE PANTHERS
> 2 | LEAGUE PANTHERS
> 3 | LEAGUE PANTHERS
> 4 | LEAGUE PANTHERS
> 5 | LEAGUE BRONCOS
> 6 | LEAGUE BRONCOS
>
> Here is my SQL so far, it will select records 1 to 5 instead
> of 1,2,3 and 5 only.
>
> Any help greatly appreciated. I think I need a Group By somewhere in
> there.
>
> select a1.id
> from aap a1
> where id < ( SELECT max(id) FROM aap AS a2 )
> AND EXISTS
> (
> SELECT *
> FROM aap AS a2
> WHERE a1.keyword = a2.keyword
> )
>
> Regards
> Rudi.
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 1: subscribe and unsubscribe
> commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete duplicates
2003-06-22 09:15 Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
2003-06-22 21:17 ` Re: Delete duplicates Franco Bruno Borghesi <franco@akyasociados.com.ar>
@ 2003-06-23 05:16 ` Denis Arh <denis@exonium.net>
2003-06-23 05:22 ` Re: Delete duplicates Tom Lane <tgl@sss.pgh.pa.us>
2003-06-23 05:22 ` Re: Delete duplicates Sean Chittenden <sean@chittenden.org>
2003-06-23 05:39 ` Re: Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
2003-06-25 08:16 ` Re: Delete duplicates Dani Oderbolz <oderbolz@ecologic.de>
0 siblings, 4 replies; 10+ messages in thread
From: Denis Arh @ 2003-06-23 05:16 UTC (permalink / raw)
To: pgsql-sql
How to delete "real" duplicates?
id | somthing
-----------------------
1 | aaa
1 | aaa
2 | bbb
2 | bbb
(an accident with backup recovery...)
Regards,
Denis Arh
----- Original Message -----
From: "Franco Bruno Borghesi" <franco@akyasociados.com.ar>
To: <rudi@oasis.net.au>
Cc: <pgsql-sql@postgresql.org>
Sent: Sunday, June 22, 2003 11:17 PM
Subject: Re: [SQL] Delete duplicates
> try this
>
> DELETE FROM aap WHERE id NOT IN (
> SELECT max(id)
> FROM aap
> GROUP BY keyword
> );
>
> >
> >
> > Hi,
> >
> > I have a table with duplicates and trouble with my SQL.
> > I'd like to keep a single record and remove older duplicates.
> > For example below of the 6 recods I'd like to keep records
> > 4 and 6.
> >
> > TABLE: aap
> > id | keyword
> > ----+-----------------
> > 1 | LEAGUE PANTHERS
> > 2 | LEAGUE PANTHERS
> > 3 | LEAGUE PANTHERS
> > 4 | LEAGUE PANTHERS
> > 5 | LEAGUE BRONCOS
> > 6 | LEAGUE BRONCOS
> >
> > Here is my SQL so far, it will select records 1 to 5 instead
> > of 1,2,3 and 5 only.
> >
> > Any help greatly appreciated. I think I need a Group By somewhere in
> > there.
> >
> > select a1.id
> > from aap a1
> > where id < ( SELECT max(id) FROM aap AS a2 )
> > AND EXISTS
> > (
> > SELECT *
> > FROM aap AS a2
> > WHERE a1.keyword = a2.keyword
> > )
> >
> > Regards
> > Rudi.
> >
> > ---------------------------(end of
> > broadcast)--------------------------- TIP 1: subscribe and unsubscribe
> > commands go to majordomo@postgresql.org
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete duplicates
2003-06-22 09:15 Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
2003-06-22 21:17 ` Re: Delete duplicates Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-06-23 05:16 ` Re: Delete duplicates Denis Arh <denis@exonium.net>
@ 2003-06-23 05:22 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2003-06-23 05:22 UTC (permalink / raw)
To: Denis Arh <denis@exonium.net>; +Cc: pgsql-sql
"Denis Arh" <denis@exonium.net> writes:
> How to delete "real" duplicates?
Use the OID or CTID system columns.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete duplicates
2003-06-22 09:15 Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
2003-06-22 21:17 ` Re: Delete duplicates Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-06-23 05:16 ` Re: Delete duplicates Denis Arh <denis@exonium.net>
@ 2003-06-23 05:22 ` Sean Chittenden <sean@chittenden.org>
3 siblings, 0 replies; 10+ messages in thread
From: Sean Chittenden @ 2003-06-23 05:22 UTC (permalink / raw)
To: Denis Arh <denis@exonium.net>; +Cc: pgsql-sql
> How to delete "real" duplicates?
>
> id | somthing
> -----------------------
> 1 | aaa
> 1 | aaa
> 2 | bbb
> 2 | bbb
>
> (an accident with backup recovery...)
I'm not 100% on some of the syntax off the top of my head, but:
BEGIN;
ALTER TABLE orig_table RENAME TO backup_table;
CREATE TABLE orig_table AS SELECT id,something FROM backup_table GROUP BY id, something;
-- Create any indexes on orig_table that need to be recreated
DROP TABLE orig_table;
COMMIT;
This isn't for the faint of heart: be sure to do this inside of a
transaction or on a backup db until you're 100% good to go. -sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete duplicates
2003-06-22 09:15 Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
2003-06-22 21:17 ` Re: Delete duplicates Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-06-23 05:16 ` Re: Delete duplicates Denis Arh <denis@exonium.net>
@ 2003-06-23 05:39 ` Rudi Starcevic <rudi@oasis.net.au>
3 siblings, 0 replies; 10+ messages in thread
From: Rudi Starcevic @ 2003-06-23 05:39 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Hi,
Would this be OK or a little crude (untested) :
INSERT INTO new_table ( id, something )
SELECT
DISTINCT ON (id)
id,
something
FROM old_table
ORDER BY id
Or something similar but create a new table ?
Cheers
Rudi.
Denis Arh wrote:
>How to delete "real" duplicates?
>
>id | somthing
>-----------------------
>1 | aaa
>1 | aaa
>2 | bbb
>2 | bbb
>
>(an accident with backup recovery...)
>
>
>
>Regards,
>Denis Arh
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Delete duplicates
2003-06-22 09:15 Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
2003-06-22 21:17 ` Re: Delete duplicates Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-06-23 05:16 ` Re: Delete duplicates Denis Arh <denis@exonium.net>
@ 2003-06-25 08:16 ` Dani Oderbolz <oderbolz@ecologic.de>
3 siblings, 0 replies; 10+ messages in thread
From: Dani Oderbolz @ 2003-06-25 08:16 UTC (permalink / raw)
To: pgsql-sql
Denis Arh wrote:
>How to delete "real" duplicates?
>
>id | somthing
>-----------------------
>1 | aaa
>1 | aaa
>2 | bbb
>2 | bbb
>
>(an accident with backup recovery...)
>
In these cases, its certainly the best to rebuild your table
using a
CREATE TABLE new AS
SELECT col1,col1..
FROM old
GROUPY BY col1,col2...
If you have got many duplicates, recreating a new table
semms better to me than deleteing.
(Another advantage is that you still have the "bad" table as a record).
Cheers,
Dani
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2003-06-25 08:16 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-22 09:15 Delete duplicates Rudi Starcevic <rudi@oasis.net.au>
2003-06-22 10:20 ` Ian Barwick <barwick@gmx.net>
2003-06-22 11:03 ` Paul Thomas <paul@tmsl.demon.co.uk>
2003-06-22 18:33 ` Germán Gutiérrez <germangutierrezg@entelchile.net>
2003-06-22 21:17 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-06-23 05:16 ` Denis Arh <denis@exonium.net>
2003-06-23 05:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-23 05:22 ` Sean Chittenden <sean@chittenden.org>
2003-06-23 05:39 ` Rudi Starcevic <rudi@oasis.net.au>
2003-06-25 08:16 ` Dani Oderbolz <oderbolz@ecologic.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox