Delete duplicates rows of table when there is no any unique column exist Script in T-SQL

Here when there are many duplicates row in table and

table do not have any unique columns

example.

suppose one table 'test' have three column.

1.tid 

2.tname 

3.tcity

and this table contain records like.

tid tname tcity

1 A G

1 A G

1 A G

1 A G

2 B B

2 B B

3 C V

3 C V

3 C V

3 C V

3 C V

Here when group by query fire like.

select tid,tname,tcity

from test

group by tid,tname,tcity

output of above query is.

tid tname tcity

1 A G

2 B B

3 C V

So here total three records exists

So when u want to delete duplicats records

from this type of table, i show i idea for it

like..

create table test

(tid int not null,

tname varchar(50) not null,

tcity varchar(50) not null

)

go

go

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(2,'B','B')

insert test values(2,'B','B')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

go

Here i show u one query which delete duplicats rows

where there is not any unique key..

 

 

delete   maindata

from 

(

select ROW_NUMBER() OVER (ORDER BY tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as maindata

where maindata.rowid not in

(

select

rowid

from (

select ROW_NUMBER() OVER (ORDER BY tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as datawithRow

where rowid in 

(

select

top 1 rowid

from (

select ROW_NUMBER() OVER (ORDER BY  tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as idatawithRow

where

idatawithRow.tid = datawithRow.tid and

idatawithRow.tname = datawithRow.tname and

idatawithRow.tcity = datawithRow.tcity

) and

datawithRow.tid = maindata.tid and

datawithRow.tname = maindata.tname and

datawithRow.tcity = maindata.tcity

)

----------------------------------------------------------------------------------------------

After executing delete query

select tid,tname,tcity

from test

group by tid,tname,tcity

Output will be...

tid tname tcity

1 A G

2 B B

3 C V