Select Query

Question

Post reply

Select Query

RR-138410

SSC Journeyman

Points: 78
More actions
April 18, 2004 at 10:35 pm

#107635

Hi,
How to find out the duplicate records without using temporary tables and cursors using sql 2000

with regds,
Kumar

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

roberthamilton Ten Centuries Points: 1002 More actions · Answer 1

Try along these lines :

select

col1, col2, col3, count(*)

from

table1

group by

cols1, col2, col3

having

count(*) > 1

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Is it just me or did the original questioner remove his question?

I see a blank message

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

Don't worry, must have been a top-secret question

I see a blank message too.

have a nice day

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Brian Cidern SSC Veteran Points: 263 More actions · Answer 4

Well, the question is there. If you look at the source code, something is preventing display. Just had to see for myself.

Anyway...

Here's a nice clean way to handle duplicate records, assuming you have a primary key set up. If not, shame on you!!

Basically, join the table onto itself, aliasing them. Your join condition should compare all the fields that need to match, deeming it a duplicate. Finally, compare the primary key fields on inequalities.

Assumption: You want to find all the duplicate records -- the ones that came in *after* the original.

SELECT a.*

FROM t_table a INNER JOIN t_table b

ON (

a.field1 = b.field1 AND

a.field2 = b.field2 AND

a.record_id > b.record_id

)

If you want to find the original records, then just reverse the operator for the primary key comparison. got it? If you want to show ALL matching records, just set the primary key comparison to != or <>.

Once you get the right blend in your condition, you can swap out the SELECT with "DELETE a" and viola, you can dump your dups.

oh, and dont forget to issue:

SELECT * INTO t_table_BAK

before you start issuing these delete.