Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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