Duplicates

[Stephen Preston]

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

[strawberry]

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