Message-ID: <4051C111.40301@yahoo.com.br>
Date: Fri, 12 Mar 2004 10:54:25 -0300
From: Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
Reply-To: email_daniel_h@yahoo.com.br
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624
MIME-Version: 1.0
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: where not unique
References: <200403121302.09243.gary.stainburn@ringways.co.uk>
In-Reply-To: <200403121302.09243.gary.stainburn@ringways.co.uk>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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;

>
>Secondly, when I run the full query I get:
>
>usedcars=# select stock_number, registration from stock
>usedcars-# where registration in
>usedcars-# (select registration, count(registration) as count from stock group 
>by registration);
>ERROR:  Subselect has too many fields
>usedcars=#
>  
>
    This is because the subselect is returning 2 columns but "in clause" 
is expecting just one column.
Try to use "exists" instead of "in", ok ?

>which is obviously because of the count field. 
>
>Can anyone tell me where I'm going wroing with these count fields?
>(I've tried renaming the field to regcount in case it was a reserved word 
>problem).
>  
>

    I hope this helps you.