Re: [SQL] where not unique

[Daniel Henrique Alves Lima]

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;
 



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings