?
Solved

how to get the duplicated records?

Posted on 2010-01-11
19
Medium Priority
?
283 Views
Last Modified: 2012-05-08
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
Comment
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
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +3
19 Comments
 
LVL 60

Expert Comment

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

Expert Comment

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

Expert Comment

by:chapmandew
ID: 26288443
distinct is not required with the group by
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 58

Accepted Solution

by:
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

by:HainKurt
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

by:jlsilva01
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

by:Castlewood
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

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

Expert Comment

by:chapmandew
ID: 26289367
Also, having is a "where" for aggregate values.
0
 
LVL 4

Expert Comment

by:jlsilva01
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

by:Anthony Perkins
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

by:chapmandew
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

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

Expert Comment

by:HainKurt
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

by:Castlewood
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.
Why did you ask this? What is your point?
Thanks.
0
 
LVL 58

Expert Comment

by:HainKurt
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

by:Castlewood
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

by:Castlewood
ID: 31675809
exact answer I look for. Thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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…

764 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