Solved

Writing a query in SQL Server  2012 showing duplicates

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 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

20 Experts available now in Live!

Get 1:1 Help Now