How to find duplicates in SQL Server

Hi guys,

I want to find duplicate records from SQL Server, however i want to include the ID field as well which is unique. For example
1 a b c d
2 a b c d
3 a b c d
4 g h i j

Open in new window

how write a query to find first three records along with their ids.

regards
Ali ShahSQL DeveloperAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
Try using count() over()

select * from (
Select *
, count(*) over(partition by col1,col2,col3,col4) as counted
From that_table) d
Where counted > 1
0
 
Pawan KumarDatabase ExpertCommented:
Try..

SELECT id, Col1,col2,col3,col4 FROM 
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY COL1,COL2,COL3,col4 ORDER BY (SELECT 1)) rnk
FROM yourTable
)k
WHERE rnk > 1

Open in new window

0
 
Ali ShahSQL DeveloperAuthor Commented:
Thanks a lot for your help. yes it worked like a magic
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.