X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 1AB61475A7F
	for <pgsql-sql@postgresql.org>; Mon,  3 Mar 2003 13:18:53 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 73460-10 for <pgsql-sql@postgresql.org>;
	Mon,  3 Mar 2003 13:18:48 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 00380475A1F
	for <pgsql-sql@postgresql.org>; Mon,  3 Mar 2003 13:18:47 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2877540; Mon, 03 Mar 2003 10:18:32 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Aaron Chu <astrate@mac.com>, pgsql-sql@postgresql.org
Subject: Re: Beginner needs help
Date: Mon, 3 Mar 2003 10:18:10 -0800
User-Agent: KMail/1.4.3
References: <96853212-4AA9-11D7-8F7C-000393017462@mac.com>
In-Reply-To: <96853212-4AA9-11D7-8F7C-000393017462@mac.com>
MIME-Version: 1.0
Message-Id: <200303031018.10219.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200303/83
X-Sequence-Number: 12123

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=20
		AND surname =3D names_table.surname );

--=20
Josh Berkus
Aglio Database Solutions
San Francisco