From: Daniel Henrique Alves Lima <email_daniel_h@yahoo.com.br>
To: Reinoud van Leeuwen <reinoud.v@n.leeuwen.net>
To: pgsql-sql@postgresql.org
Subject: Re: where not unique
Date: Fri, 12 Mar 2004 11:05:19 -0300
Message-ID: <4051C39F.2060403@yahoo.com.br> (raw)
In-Reply-To: <20040312145048.O636@spoetnik.xs4all.nl>
References: <200403121302.09243.gary.stainburn@ringways.co.uk>
<4051C111.40301@yahoo.com.br>
<20040312145048.O636@spoetnik.xs4all.nl>
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;
>
>
>
>
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, reinoud.v@n.leeuwen.net
Subject: Re: where not unique
In-Reply-To: <4051C39F.2060403@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