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

Select and Display Duplicate Records

I need to select and display using SQL duplicate records from the following table

bomparent  bompos
12                 1
12                  2
13                 1
13                  2
12                  1
12                  2
13                 3

I need to return
12       1
12       2
12       1
12        2

from the above table.

Thanks
0
RickCooper
Asked:
RickCooper
  • 4
  • 3
1 Solution
 
BrandonGalderisiCommented:
The first query will only what is duplicated, not every duplicate record
12    1
12    2


the second query will return all items that have duplicates
12    1
12    2
12    1
12    2


select bomparent,bompos
from YourTable
group by bomparent,bompos
having count(*)>1
 
 
GO
 
select a.* from YourTable
join 
(select bomparent,bompos
from YourTable
group by bomparent,bompos
having count(*)>1) b
on a.bomparent = b.bomparent
and a.bompos = b.bompos

Open in new window

0
 
RickCooperAuthor Commented:
Hi BrandonGalderisi
The second query is what is required as I need to delete the duplicates. However it fails with The column prefix 'a' does not match with a table name or alias name used in the query.

0
 
BrandonGalderisiCommented:
oops...

forgot the alias after "YourTable" which you obviously need to replace with your actual table name.
select a.* from YourTable a
join 
(select bomparent,bompos
from YourTable
group by bomparent,bompos
having count(*)>1) b
on a.bomparent = b.bomparent
and a.bompos = b.bompos

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RickCooperAuthor Commented:
Spot on. Thanks very much.
I now have over 17000 records to delete.
0
 
BrandonGalderisiCommented:
So you are deleting one of the two I assume.  And you're not on SQL 2000 by chance are you?
0
 
BrandonGalderisiCommented:
Sorry....

And you're not on SQL "2005" by chance are you?
0
 
RickCooperAuthor Commented:
I'm deleting one of the two and I'm on SQL2000
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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