Re: how to look for duplicate rows?

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