Solved

Writing a query in SQL Server  2012 showing duplicates

Posted on 2014-11-25
4
80 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now