Solved

Writing a query in SQL Server  2012 showing duplicates

Posted on 2014-11-25
4
83 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
[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
  • 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

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

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

739 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