Solved

Search for duplicate values

Posted on 2012-03-26
5
226 Views
Last Modified: 2012-06-21
This is a MSSQL 2005 database.  

I'm trying to generate a result set that contains DocIDs where ObjectIDs for that specific DocID have at least one duplicate.  Please note the rows between the *****.  

Sample data:
DocID|PageNum|SubPageNum|ObjectID
1192077|1|0|2090063
1192077|2|0|2090064
1192077|3|0|2090065
1192078|1|0|2090067
1192078|2|0|2090068
1192079|1|1|2090070
1192079|2|0|2090071
1192080|1|1|2090073
1192080|2|0|2090074
**********************
1192081|1|0|2090076
1192081|2|1|2090077
1192081|3|2|2090077
1192081|4|3|2090077
1192081|5|4|2090077
1192081|6|5|2090077
1192081|7|6|2090077
1192081|8|1|2090078
1192081|9|2|2090078
1192081|10|3|2090078
1192081|11|4|2090078
1192081|12|5|2090078
1192081|13|6|2090078
1192081|14|7|2090078
1192081|15|8|2090078
1192081|16|9|2090078
1192081|17|10|2090078
1192081|18|11|2090078
1192081|19|12|2090078
1192081|20|13|2090078
1192081|21|14|2090078
1192081|22|15|2090078
1192081|23|16|2090078
1192081|24|17|2090078
1192081|25|18|2090078
**********************
1192082|1|1|2090080
1192082|2|0|2090081
1192083|1|0|2090083
1192083|2|0|2090084
1192084|1|1|2090086
1192084|2|0|2090087
1192085|1|1|2090089
1192085|2|0|2090090
1192086|1|1|2090092
1192086|2|0|2090093


You'll see that there are duplicate ObjectIDs for the 1192081 DocID.  So the desired result set would be:

Result set:
1192081|1|0|2090076
1192081|2|1|2090077
1192081|3|2|2090077
1192081|4|3|2090077
1192081|5|4|2090077
1192081|6|5|2090077
1192081|7|6|2090077
1192081|8|1|2090078
1192081|9|2|2090078
1192081|10|3|2090078
1192081|11|4|2090078
1192081|12|5|2090078
1192081|13|6|2090078
1192081|14|7|2090078
1192081|15|8|2090078
1192081|16|9|2090078
1192081|17|10|2090078
1192081|18|11|2090078
1192081|19|12|2090078
1192081|20|13|2090078
1192081|21|14|2090078
1192081|22|15|2090078
1192081|23|16|2090078
1192081|24|17|2090078
1192081|25|18|2090078
0
Comment
Question by:Whah
  • 3
5 Comments
 
LVL 13

Accepted Solution

by:
Ashok earned 500 total points
ID: 37767354
select DocID,PageNum,SubPageNum,ObjectID
from myTable where DocID in
(select distinct DocID from myTable
group by DocID, ObjectID
having count(*) > 1);
0
 
LVL 18

Expert Comment

by:lludden
ID: 37767374
First, strip out all the other columns to get your unique docID/OjectID pairs
Then find Duplicate DocID
Then display full list of just those DocID

With UniqueList AS (SELECT DISTINCT DocID, ObjectID FROM MyTable),
DupList AS (SELECT DocID FROM UniqueList GROUP BY DocID HAVING COUNT(*) >1)
SELECT myTable.*
FROM myTable
INNER JOIN DupeList ON MyTable.DocID = DupList.DocID

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
ID: 37767523
Can't follow the requirement.
I see the result set is the same as that between the asterisks.

Six records as input and required output may clear the confusion.
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37767640
Above the ResultSet is all records.

He wants the ResultSet just as he mentioned.

The requirement is very clear.

I see the result set is the same as that between the asterisks.
Because that is what he wants (it is correct ResultSet).

Ashok
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37767716
First group of data
2090063, 2090064, 2090065, 2090067, 2090068, 2090070, 2090071, 2090073, 2090074
does not have any duplicate.

Same for last group of data.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…

911 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

17 Experts available now in Live!

Get 1:1 Help Now