From: Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: where not unique
Date: Fri, 12 Mar 2004 10:54:25 -0300
Message-ID: <4051C111.40301@yahoo.com.br> (raw)
In-Reply-To: <200403121302.09243.gary.stainburn@ringways.co.uk>
References: <200403121302.09243.gary.stainburn@ringways.co.uk>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: email_daniel_h@yahoo.com.br, gary.stainburn@ringways.co.uk
Subject: Re: where not unique
In-Reply-To: <4051C111.40301@yahoo.com.br>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox