• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

how to get the duplicated records?

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
Castlewood
Asked:
Castlewood
  • 5
  • 4
  • 4
  • +3
2 Solutions
 
chapmandewCommented:
select partno, rev, count(*)
from inmast
group by partno, rev
having count(*) > 1
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT DISTINCT partno, rev FROM inmast GROUP BY partno, rev HAVING COUNT(*) > 1
0
 
chapmandewCommented:
distinct is not required with the group by
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
HainKurtSr. System AnalystCommented:
here it is:

SELECT partno
FROM inmast
GROUP BY partno
HAVING COUNT(*) > 1
0
 
HainKurtSr. System AnalystCommented:
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
 
jlsilva01Commented:
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
 
CastlewoodAuthor Commented:
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
 
chapmandewCommented:
If our results come out empty then you don't have dupes. :)
0
 
chapmandewCommented:
Also, having is a "where" for aggregate values.
0
 
jlsilva01Commented:
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
 
Anthony PerkinsCommented:
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
 
chapmandewCommented:
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
 
Anthony PerkinsCommented:
I know what you mean, I always assume there is a different assumption made by each contributor. :)
0
 
HainKurtSr. System AnalystCommented:
"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
 
CastlewoodAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
CastlewoodAuthor Commented:
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
 
CastlewoodAuthor Commented:
exact answer I look for. Thanks.
0
 
Anthony PerkinsCommented:
>>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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now