X-Original-To: pgsql-sql@postgresql.org Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id 4BE3E474E42 for ; Sun, 9 Mar 2003 17:42:12 -0500 (EST) Received: by news.hub.org (Postfix, from userid 8) id 3487943A67A; Sun, 9 Mar 2003 17:42:12 -0500 (EST) From: Lee Harr X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Beginner needs help Date: Sun, 9 Mar 2003 22:42:12 +0000 (UTC) Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 22 Message-ID: References: <96853212-4AA9-11D7-8F7C-000393017462@mac.com> Reply-To: missive@hotmail.com Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: slrn/0.9.7.3 (FreeBSD) To: pgsql-sql@postgresql.org X-Archive-Number: 200303/248 X-Sequence-Number: 12288 In article , Björn Lundin wrote: > Aaron Chu wrote: > >> Hi, >> >> 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. > > select surname, count('a') from table > group by surname > having count('a') > 1 > order by surname > SELECT DISTINCT surname FROM table t1, table t2 WHERE t1.surname = t2.surname AND t1.oid != t2.oid ORDER BY surname;