Solved

Search for duplicate values

Posted on 2012-03-26
5
230 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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
MS SQL Update query with connected table data 3 38
Where on a calculated field 1 21
SQL Query 2 31
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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