Solved

Writing a query in SQL Server  2012 showing duplicates

Posted on 2014-11-25
4
82 Views
Last Modified: 2014-11-25
Hello experts,

I am trying to write a query against a single table in SQL Server 2012. The purpose is to find duplicates in the MachineID field.

The following is the first query and it gets me 26 records:

SELECT MachineID , Caption00, Name00,Version00, RevisionID,Distributed00
FROM     Operating_System_DATA
WHERE  Caption00 = 'Microsoft Windows XP Professional'

The second query gets me 20 records and show me a count of duplicated records but doesn't show me the MachineID field which I need to identify each record:


SELECT Caption00, Name00,Version00, RevisionID,Distributed00, count(MachineID) as count
FROM  Operating_System_DATA
WHERE   Caption00 = 'Microsoft Windows XP Professional'
Group by Caption00,Name00,Version00,RevisionID,Distributed00

My third query shows me 5 rows but the count for each row gives a total of 11 rows.


SELECT  Caption00, Name00,Version00, RevisionID, count(*) as count
FROM    Operating_System_DATA
WHERE  Caption00 = 'Microsoft Windows XP Professional'
Group by Caption00,Name00,Version00,RevisionID
having count(*) > 1

Obviously I don't know what I am doing. But what I am trying to do is get a set that shows me the duplicated records, this requires the values in the MachineID field to be visible.

Thank you
0
Comment
Question by:adamtrask
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40465403
SELECT MachineID, count(MachineID) over(partition by caption00, name00, version00, revisionid00 order by MachineID rows between unbounded preceding and unbounded following) as myCount
From Operating_system_data
Where

Etc..
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 40465404
Subquery to get the duplicate Caption00's, main query to grab the
SELECT os.Caption00, os.MachineID, os.blah, os.blah, os.blah,
FROM Operating_System_DATA os
JOIN (
   SELECT  Caption00, count(Caption00) as the_count
   FROM    Operating_System_DATA
   Group by Caption00
   HAVING count(Caption00) > 1) os_count ON os.Caption00 = os_count.Caption00
ORDER BY os.Caption00

Open in new window

Couple of articles that can help
SQL Server Deleting Duplicate Rows
SQL Server GROUP BY Solutions (your question is point #5)
0
 

Author Closing Comment

by:adamtrask
ID: 40465499
Thank you both very much
0
 
LVL 65

Expert Comment

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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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