Обсуждение: Selecting duplicates

Somehow, I've managed to get duplicate entries in my soon-to-be primary
key field. How can I select for duplicates in a field? I know how to
select for blank and NULL, but duplicates escape me.

Thanks,
Gary

**************************************************************************
* Gary B. Hoffman, Computing Services Manager  e-mail: ghoffman@ucsd.edu *
* Graduate School of International Relations and Pacific Studies (IR/PS) *
* University of California, San Diego (UCSD)       voice: (858) 534-1989 *
* 9500 Gilman Dr., La Jolla, CA 92093-0519 USA       fax: (858) 534-3939 *
**************************************************************************

Gary Hoffman wrote:
>
> Somehow, I've managed to get duplicate entries in my soon-to-be primary
> key field. How can I select for duplicates in a field? I know how to
> select for blank and NULL, but duplicates escape me.

Sorry, I only know complicated ways of doing this. The way I usually do
it is to create a temporary table:

create temp table tmp (id int4, cnt int4);
insert into tmp select id, count(*) from <table> group by id;

Then look at all entries in tmp where cnt is bigger than 1. Deciding
which entry to throw out is tougher. I have been working with the
assumption that oids are (usually anyway) assigned in ascending order.
Don't actually know whether that is true. But if they are you can delete
everything but the entry with the highest (or lowest) oid.

Hope this helps,

Adriaan

Hi Garry

Perhaps following will help you (asuming that "id" is your soon-to-be
primary key):

select oid, id from tab a where exists
(select oid from tab b where b.id=a.id and b.oid<>a.oid)
order by id

Sincerely yours, Yury.
don.web-page.net, ICQ 11831432

Gary Hoffman wrote:
>
> Somehow, I've managed to get duplicate entries in my soon-to-be primary
> key field. How can I select for duplicates in a field? I know how to
> select for blank and NULL, but duplicates escape me.
>
> Thanks,
> Gary