Duplicates

Hi

I wish to perform a search on a column of a table. The column contains
surnames. I wish the results to return the entries which contain more than
one instance of a surname. eg.

ID__Surname
1___Jones
2___Smith
3___Jones
4___Carter
5___Jones
6___Wilson
7___Smith

The result I'm after is

1___Jones
3___Jones
5___Jones
2___Smith
7___Smith

Is this possible with one query? and if so what?

Thanks

Stephen

Re: Duplicates

Stephen Preston wrote:
> Hi
>
> I wish to perform a search on a column of a table. The column contains
> surnames. I wish the results to return the entries which contain more than
> one instance of a surname. eg.
>
> ID__Surname
> 1___Jones
> 2___Smith
> 3___Jones
> 4___Carter
> 5___Jones
> 6___Wilson
> 7___Smith
>
> The result I'm after is
>
> 1___Jones
> 3___Jones
> 5___Jones
> 2___Smith
> 7___Smith
>
> Is this possible with one query? and if so what?
>
> Thanks
>
> Stephen

Can you use subqueries? If so, this would work:

SELECT t1.id, t1.lastname
FROM (

SELECT DISTINCT lastname, count( lastname )
FROM people
GROUP BY lastname
HAVING count( lastname ) >1
)t2
LEFT JOIN people t1 ON t1.lastname = t2.lastname