Re: Beginner needs help

Aaron,

Thanks for an easy one:

> I have a table which has a column of surnames (string) and I would like
> to know how can I retrieve (SELECT) all the repeated surnames, i.e.
> more than one person who has the same surname.

To just get a list of repeated surnames:

SELECT surname, count(*) as names_count
FROM names_table
GROUP BY surname
HAVING count(*) > 1

To get the records these are attached to, call the above as a subquery:

SELECT names_table.*
FROM names_table
WHERE EXISTS ( SELECT surname, count(*)
FROM names_table
GROUP BY surname
HAVING count(*) > 1
AND surname = names_table.surname );

--
Josh Berkus
Aglio Database Solutions
San Francisco