SQL Query


I need to write a query to check for duplicates in data..

Columns of table
ItemNumber,  ProductNumber, Country, List, Contract, DateCode, Cdate, AddDate, ModifyDate


The conditions for duplicates is that ItemNumber,  ProductNumber,Cdate,
Must be unique

I Need to select these records



LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
 
chapmandewCommented:
try this:

with cte
as
(
select * from (
select ranking = dense_rank() over(partition by itemnumber, productnumber, cdate order by newid()), *
from Pricing
) a
)

delete from cte
where ranking > 1
0
 
chapmandewCommented:
select * from (
select ranking = dense_rank() over(partition by itemnumber, productnumber, cdate order by newid())
from tablename
) a
where ranking > 1

shows you the dupes.
0
 
Leo TorresSQL DeveloperAuthor Commented:
Ok Cool.. this only selects the rank I need to select the complete row of the table to see what info is dup

Sorry i did not metion name of table below is the query I ran

thanks

select * from (
select ranking = dense_rank() over(partition by itemnumber, productnumber, cdate order by newid())
from Pricing
) a
where ranking > 1

Open in new window

0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
chapmandewCommented:
sorry...forgot to get the rest:

select * from (
select ranking = dense_rank() over(partition by itemnumber, productnumber, cdate order by newid()), *
from tablename
) a
where ranking > 1
0
 
Leo TorresSQL DeveloperAuthor Commented:
That great.. I verified the data.. Now I tried to run a delete but i wont work

Errors
Server Msg: 102, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Incorrect syntax near '('.
Server Msg: 102, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Incorrect syntax near 'a'.

is it that much different to delete??
Delete from (
select ranking = dense_rank() over(partition by itemnumber, productnumber, cdate order by newid()), *
from Pricing
) a
where ranking > 1

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
WOW.... That's freakin cool.. Thanks worked like a charm!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.