Thread: how to look for duplicate rows?

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

using 8.1.

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@iol.ie