Solved

# how to get the duplicated records?

Posted on 2010-01-11
Medium Priority
283 Views
With the following command, I will get total 3,000 records:
SELECT DISTINCT partno, rev FROM inmast
With the following command, I will get total 2,985 records:
SELECT DISTINCT partno FROM inmast
That means there are at most 15 parts with multiple revisions. How to get a list of parts with multiple revisions?
0
Question by:Castlewood
[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
• 5
• 4
• 4
• +3

LVL 60

Expert Comment

ID: 26288435
select partno, rev, count(*)
from inmast
group by partno, rev
having count(*) > 1
0

LVL 75

Expert Comment

ID: 26288436
SELECT DISTINCT partno, rev FROM inmast GROUP BY partno, rev HAVING COUNT(*) > 1
0

LVL 60

Expert Comment

ID: 26288443
distinct is not required with the group by
0

LVL 58

Accepted Solution

HainKurt earned 2000 total points
ID: 26288467
here it is:

SELECT partno
FROM inmast
GROUP BY partno
HAVING COUNT(*) > 1
0

LVL 58

Assisted Solution

HainKurt earned 2000 total points
ID: 26288484
to get full details, you can use this

select * from inmast
where partno in (
SELECT partno
FROM inmast
GROUP BY partno
HAVING COUNT(*) > 1
) order by partno, rev
0

LVL 4

Expert Comment

ID: 26288620
SELECT DISTINCT partno, rev INTO #table1 FROM inmast ;

SELECT DISTINCT partno INTO #table2 FROM inmast ;

SELECT #table1.partno, #table2.partno
FROM #table1
LEFT OUTER JOIN #table2
ON #table1.partno = #table2.partno ;

The result with #table2.partno = NULL are your 15 rows.
0

Author Comment

ID: 26289283
chapmandew's and aneeshattingal's come out with empty.
jlsilva01's gets all rows. No row is Null.
HainKurt's gets the exact result.
Can someone tell me why the following "HAVING" cannot be replaced with "WHERE" ?
SELECT partno
FROM inmast
GROUP BY partno
HAVING COUNT(*) > 1
0

LVL 60

Expert Comment

ID: 26289363
If our results come out empty then you don't have dupes. :)
0

LVL 60

Expert Comment

ID: 26289367
Also, having is a "where" for aggregate values.
0

LVL 4

Expert Comment

ID: 26289459
You cannot using aggregate functions inside the WHERE clause.
If you want to use a any aggregate function as a filter of your query ( like WHERE ), you can only use inside the HAVING clause.
0

LVL 75

Expert Comment

ID: 26289491
Tim,

>>If our results come out empty then you don't have dupes. <<
Yes and no.  There are no partno, rev duplicates, but there should be some partno duplicates.

Agree?
Anthony
0

LVL 60

Expert Comment

ID: 26289589
Hey Anthony, sure...there could be partno dupes...but the original question was "How to get a list of parts with multiple revisions".  To me, that question begs for a partno, rev grouping....but, lord knows I've been wrong before when trying to figure out what is wanted.  :)
0

LVL 75

Expert Comment

ID: 26289664
I know what you mean, I always assume there is a different assumption made by each contributor. :)
0

LVL 58

Expert Comment

ID: 26289802
"HainKurt's gets the exact result."

whats the problem with 26288467 & 26288484? how many records do you get?

whats the PK of your table? do you have any records that is null on partno or rev?

SELECT * FROM inmast where partno is null
SELECT * FROM inmast where rev is null
SELECT * FROM inmast where partno is null and revno is null
SELECT * FROM inmast where partno is null or revno is null

how many records do you get for these queries?

0

Author Comment

ID: 26292978
HainKurt:
26288467 gets 15 records while 26288484 gets 30 records.
Those four SELECTs gets 0 record. No null in those fields.
"whats the PK of your table? do you have any records that is null on partno or rev?" The PK is partno+rev.
Thanks.
0

LVL 58

Expert Comment

ID: 26294515
ok, it means there are 15 duplicates, each one has two (15 * 2 = 30)

26288467 gets 15 records while 26288484 gets 30 records, which you are asking for...

so why don't you close this question ;)
0

Author Comment

ID: 26294887
HainKurt:
But you were asking about NULL. Do you think it will affect the answer and is something I have to pay attention to in the future?
0

Author Closing Comment

ID: 31675809
exact answer I look for. Thanks.
0

LVL 75

Expert Comment

ID: 26299641
>>Do you think it will affect the answer and is something I have to pay attention to in the future? <<
No it will not affect the answer and yes you should pay attention to it in future.

Now, can you please close the question appropriately and move on.
0

## Featured Post

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/weâ€¦
###### Suggested Courses
Course of the Month8 days, 18 hours left to enroll