Solved

How to find duplicates in SQL Server

Posted on 2016-11-18
3
57 Views
Last Modified: 2016-11-18
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
0
Comment
Question by:shah36
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41892688
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41892691
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
 

Author Closing Comment

by:shah36
ID: 41892703
Thanks a lot for your help. yes it worked like a magic
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question