?
Solved

Search for duplicate values

Posted on 2012-03-26
5
Medium Priority
?
241 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
[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
  • 3
5 Comments
 
LVL 13

Accepted Solution

by:
Ashok earned 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

777 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