X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7CFFDD1B4B7 for ; Fri, 12 Mar 2004 13:57:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 20645-06 for ; Fri, 12 Mar 2004 09:57:45 -0400 (AST) Received: from localhost.localdomain (unknown [200.245.23.5]) by svr1.postgresql.org (Postfix) with ESMTP id 60ED2D1D554 for ; Fri, 12 Mar 2004 09:55:42 -0400 (AST) Received: from yahoo.com.br (localhost.localdomain [127.0.0.1]) by localhost.localdomain (8.12.8/8.12.8) with ESMTP id i2CE5JLb002089; Fri, 12 Mar 2004 11:05:25 -0300 Message-ID: <4051C39F.2060403@yahoo.com.br> Date: Fri, 12 Mar 2004 11:05:19 -0300 From: Daniel Henrique Alves Lima Reply-To: email_daniel_h@yahoo.com.br User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Reinoud van Leeuwen , pgsql-sql@postgresql.org Subject: Re: where not unique References: <200403121302.09243.gary.stainburn@ringways.co.uk> <4051C111.40301@yahoo.com.br> <20040312145048.O636@spoetnik.xs4all.nl> In-Reply-To: <20040312145048.O636@spoetnik.xs4all.nl> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/147 X-Sequence-Number: 17245 You're right, Reinoud. Sorry for my mystake... Reinoud van Leeuwen wrote: >On Fri, Mar 12, 2004 at 10:54:25AM -0300, Daniel Henrique Alves Lima wrote: > > >>Gary Stainburn wrote: >> >> >> >>>Hi folks, >>> >>>I've got a table which contains amongst other things a stock number and a >>>registration number. I need to print out a list of stock number and reg >>>number where reg number is not unique (cherished number plate transfer not >>>completed). I've tried variations of a theme based on >>> >>>select stock_number, registration from stock where registration in >>> (select registration, count(registration) as count >>> from stock where count > 1 group by registration); >>> >>>but I have two problems. Firstly with the sub-select I get: >>> >>>usedcars=# select registration, count(registration) as count from stock where >>>count > 1 group by registration; >>>ERROR: Attribute 'count' not found >>>usedcars=# >>> >>>although if I miss out the where clause I get the expected results. >>> >>> >>> >> I think that this is not the best way, but : >> >>select * from (select registration, count(registration) as counter from >>stock group by registration) >>where counter > 1; >> >> > >Isn't that the same as > >select registration >from stock >group by registration >having count (registration) > 1; > > > >