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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AshokCommented:
select DocID,PageNum,SubPageNum,ObjectID
from myTable where DocID in
(select distinct DocID from myTable
group by DocID, ObjectID
having count(*) > 1);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.