Solved

sql for duplicate rows in table

Posted on 2013-06-04
7
308 Views
Last Modified: 2013-06-05
hi experts

I have table Customer with 4 columns and 100 rows of data.
Is there a handy sql to check if there are duplicate rows of data?

thanks
0
Comment
Question by:royjayd
[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
7 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 325 total points
ID: 39220305
Give this a whirl < air code > ...

SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39220307
yes, do the below
;with cte
(
 select row_number() over(parition by <all your columns> order by (select 1)) rn,* from <your table>
)
select * from cte where rn>1

Open in new window


in the above code, replace the
<all your columns> with the columns list of your table
<your table> with your table name.
0
 

Author Comment

by:royjayd
ID: 39220466
<<SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC >>

looks good, i am trying to get a total sum of the duplicate rows
but this doesnt seem to work

SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows,
SUM(duplicate_rows)
FROM YourTable
GROUP BY Column1, Column2, Column3, Column4
HAVING COUNT(Column4) > 1
ORDER BY COUNT(Column4) DESC

thanks
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 41

Expert Comment

by:Sharath
ID: 39220552
try this.
SELECT Column1,Column2,Column3,Column4,duplicate_rows,
      SUM(duplicate_rows) OVER ( partition BY (SELECT 1)) sum_duplicate_rows
  FROM (SELECT Column1,Column2,Column3,Column4,COUNT(Column4) AS duplicate_rows 
          FROM YourTable 
         GROUP BY Column1,Column2,Column3,Column4 
        HAVING COUNT(Column4) > 1) t1 
 ORDER BY duplicate_rows DESC 

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39220573
<somewhat redundant with the above comments>

>i am trying to get a total sum of the duplicate rows
Throw the whole thing in a subquery, and sum.  
Replace the SUM with COUNT if you want distinct duplicate row columns.

SELECT SUM(a.duplicate_rows)
FROM (
   SELECT Column1, Column2, Column3, Column4, Count(Column4) as duplicate_rows,
   SUM(duplicate_rows)
   FROM YourTable
   GROUP BY Column1, Column2, Column3, Column4
   HAVING COUNT(Column4) > 1
   ORDER BY COUNT(Column4) DESC ) a
0
 

Author Closing Comment

by:royjayd
ID: 39223358
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39223431
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

728 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