Search for duplicate values

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
WhahAsked:
Who is Participating?
 
AshokConnect With a Mentor Commented:
select DocID,PageNum,SubPageNum,ObjectID
from myTable where DocID in
(select distinct DocID from myTable
group by DocID, ObjectID
having count(*) > 1);
0
 
lluddenCommented:
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
 
hnasrCommented:
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
 
AshokCommented:
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
 
AshokCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.