Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 selecting from subquery results

Author  Topic 

psterritt
Starting Member

2 Posts

Posted - 2009-05-29 : 17:11:22
I want to find out if a column has duplicate values in a table. If I remember right, when I was working in Oracle, I could select from the results of a subquery to accomplish this --

select ProductName from
(select ProductName, count(*) as RC
from ProductsOrdered
group by ProductName)
where RC > 1;

but this doesn't seem to work in SQL Server 2005?

Jhardin
Starting Member

1 Post

Posted - 2009-05-29 : 17:22:02
try this
declare @product table
(
ProductName varchar(255)
)
insert into @product values('Product 1')
insert into @product values('Product 2')
insert into @product values('Product 3')
insert into @product values('Product 2')

select ProductName from @product
group by ProductName having count(ProductName) > 1

Its 2am do you know where your data is?
Go to Top of Page

kira
Starting Member

17 Posts

Posted - 2009-05-29 : 17:26:22
you need give your subquery an alias :

select ProductName from
(select ProductName, count(*) as RC
from ProductsOrdered
group by ProductName) temp
where RC > 1
Go to Top of Page

psterritt
Starting Member

2 Posts

Posted - 2009-05-29 : 17:32:53
Thanks, Kira and Jhardin!
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-29 : 22:45:04
[code]
try this one too works from sql2005 and above

SELECT productname FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY productname ORDER BY productname)AS rid, productname
FROM @product
)s WHERE rid > 1
[/code]
Go to Top of Page
   

- Advertisement -