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.
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 RCfrom ProductsOrderedgroup 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 thisdeclare @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 @productgroup by ProductName having count(ProductName) > 1Its 2am do you know where your data is? |
|
|
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 RCfrom ProductsOrderedgroup by ProductName) tempwhere RC > 1 |
|
|
psterritt
Starting Member
2 Posts |
Posted - 2009-05-29 : 17:32:53
|
Thanks, Kira and Jhardin! |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-05-29 : 22:45:04
|
[code]try this one too works from sql2005 and aboveSELECT productname FROM (SELECT ROW_NUMBER() OVER(PARTITION BY productname ORDER BY productname)AS rid, productnameFROM @product)s WHERE rid > 1[/code] |
|
|
|
|
|