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
LVL 1
RickCooperAsked:
Who is Participating?
 
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.