MS SQL Tips & Tricks

How to find duplicates in the table

There are several possible ways of finding duplicates in table. The first solution looks for the similar record to each one that has different Identity. That is enough to make sure that this records are duplicates.

select *
from  Table t
where exists(
      Select t2.Id from Table t2
      where t.Column=t2.Column and
           t.Id<>t2.Id   
   )

Second way does the same thing but uses subquery instead of exist clause.

select *
from Table t
inner join (
   select count(*) as Items, Column from Table
   group by Column
   having Count(*)>1   
) as pom on (pom.Column=t.Column)

In both queries you can always extend the condition

select *
from Table t
inner join (
   select count(*) as Items, Column1, COlumn2, Column3 
   from Table
   group by Column1, COlumn2, Column3
   having Count(*)>1   
) as pom on (pom.Column1=t.Column1) and
	(pom.Column2=t.Column2)
	(pom.Column3=t.Column3)

Learn more tricks

Add Comment

Comments

25th July 2021

safe cialis online

icosype

24th August 2021

tamoxifen citrate tab tamoxifen citrate molecular weight nolvadex peptide tamoxifen and menstrual cycle nolvadex and clomid pct nolvadex pct cycle

HoraceBerve

1st September 2021

topical azithromycin erythromycin obstetrics zithromax emedicine flagyl 400 omnicef dose zithromax classified

Scottunrek

4th September 2021

Fungus In Turtles Amoxicillin

Cialis

8th September 2021

will generic viagra available

Invorne

10th September 2021

bystolic detrol cleocin diamox cymbalta benemid

Harveybot

16th September 2021

https://buypriligyhop.com/ - cheap priligy

Stubmag

19th September 2021

buy viagra online legally

JudyLoalf

19th September 2021

hydroxychloroquine sulfate tab 200 mg

CarlLoalf

19th September 2021

otc viagra usa

EvaLoalf

20th September 2021

priligy 30mg tablets

Luhteerge