Skip site navigation (1) Skip section navigation (2)

Re: how to look for duplicate rows?

From: Raymond O'Donnell <rod(at)iol(dot)ie>
To: zach cruise <zachc1980(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: how to look for duplicate rows?
Date: 2010-01-29 16:54:20
Message-ID: 4B6312BC.9020409@iol.ie (view raw or flat)
Thread:
Lists: pgsql-general
On 29/01/2010 16:40, zach cruise wrote:
> i have to clean a table that looks like so:
> 
> create table test (sn integer, fname varchar(10), lname varchar(10));
> insert into test values (1, 'adam', 'lambert');
> insert into test values (2, 'john', 'mayer');
> insert into test values (3, 'john', 'mayer');
> insert into test values (4, 'mary', 'kay');
> insert into test values (5, 'john', 'mayer');
> insert into test values (6, 'susan', 'boyle');
> insert into test values (7, 'susan', 'boyle');
> insert into test values (8, 'mark', 'ingram');
> 
> for that, i need to run a query that returns like so:
> 
> result:
> is_not_distinct
> 2, 3, 5
> 6, 7

How about something like:

  select fname, lname from test
  group by fname, lname
  having count(1) > 1

...which gives you the names at least.

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

In response to

pgsql-general by date

Next:From: Joshua D. DrakeDate: 2010-01-29 17:25:34
Subject: Re: [GENERAL] Versions RSS page is missing version(s)
Previous:From: Dave PageDate: 2010-01-29 16:51:44
Subject: Re: [GENERAL] Versions RSS page is missing version(s)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group