Received: from rossini.ednet.co.uk (root@rossini.ednet.co.uk [212.20.226.132]) by hub.org (8.9.3/8.9.3) with ESMTP id GAA22897 for ; Fri, 7 Apr 2000 06:46:26 -0400 (EDT) (envelope-from allan.kelly@buildstore.co.uk) Received: from qube.buildstore.office (dialup120.ednet.co.uk [212.20.227.183]) by rossini.ednet.co.uk (8.9.3/8.9.3) with ESMTP id LAA14426 for ; Fri, 7 Apr 2000 11:45:48 +0100 Received: from buildstore.co.uk (babyb.buildstore.office [192.168.1.78]) by qube.buildstore.office (8.9.2/8.9.2) with ESMTP id LAA27220 for ; Fri, 7 Apr 2000 11:41:16 +0100 (BST) Message-ID: <38EDB999.EA91E2DB@buildstore.co.uk> Date: Fri, 07 Apr 2000 11:34:01 +0100 From: Allan Kelly Organization: buildstore X-Mailer: Mozilla 4.6 [en-gb]C-CCK-MCD NetscapeOnline.co.uk (Win98; I) X-Accept-Language: en-GB,en MIME-Version: 1.0 To: "pgsql-sql@postgresql.org" Subject: duplicates Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200004/64 I have a system bug which means that I have occasional dupicate entries in my 'subscribers' table. I'm trying to find a query which performs something like a 'where user_name is not unique' query. At the moment we use this cludge: select count(*), user_name from subscribers group by user_name order by count; (I'm surprised I can't add 'where count > 1' but it seems conditionals on aggregate fields are not allowed). This gives me a very long list with the 'not unique' entries at the bottom, eg count | user_name ------+------------------ 1 | bill.hicks [ ..cut 9 zillion results.. ] 1 | margaret.thatcher 4 | linus.torvalds 9 | bill.gates I then have to do select oid from subscribers where user_name = 'linus.torvalds'; and delete all but one of the records. Is there a better way to do this? And yes, we're working on a system fix to avoid the problem in the 1st place! TIA, al. -- # Allan Kelly http://www.plotsearch.co.uk # (+44) (0)131 524 8500 # allan.kelly@buildstore.co.uk... .. # /Software Engineer/i . . . . . # ------------------------------ * . . . . . # "If you are a Visual Basic programmer, * . . . # these details are none of your business." * . . . # Mr Bunny's Guide to Active X, by Carlton Egremont III * . . # ------------------------------ vi: set noet tw=80 sts=4 ts=8 : .