SQL Duplicate data displays only one

--Processing table duplicate records (query and delete)
/************************************************************************************************************** ****************************************
1, Num, name of the same duplicate value record, no size relationship only keep one
2, name is the same, when the ID has a size relationship, keep either large or small one of the records
Finishing Man: China Wind (Roy)

Date: 2008.06.06
*************************************************************************************************************** ***************************************/

--1, used for querying duplicate records (if the column has no size relationship when 2000 is used to generate self-increment columns and temporary table processing, SQL2005 is handled with the Row_number function)

----(Roy) generates test numbers

If not object_id (' Tempdb. #T ') is null
drop table #T
Go
Create table #T ([ID] int,[name] nvarchar (1), [Memo] nvarchar (2))
Insert #T
Select 1,n ' A ', N ' A1 ' UNION ALL
Select 2,n ' A ', N ' A2 ' UNION ALL
Select 3,n ' A ', N ' A3 ' UNION ALL
Select 4,n ' B ', N ' B1 ' UNION ALL
Select 5,n ' B ', N ' B2 '
Go

------the record with the lowest ID of the same name (recommended in 3), the efficiency is higher than 1, 2 when SQL05
Method 1:
SELECT * from #T a where NOT exists (select 1 from #T where Name=a.name and Id<a.id)

Method 2:
Select A.* from #T a join (select min (ID) id,name from #T Group by Name) B on A.name=b.name and a.id=b.id

Method 3:
SELECT * from #T a where id= (the Select min (ID) from #T where Name=a.name)

Method 4:
Select A.* from #T a join #T B on A.name=b.name and A.id>=b.id GROUP by A.id,a.name,a.memo have count (1) =1

Method 5:
SELECT * FROM #T a GROUP by Id,name,memo have id= (select min (ID) from #T where Name=a.name)

Method 6:
SELECT * from #T a WHERE (select COUNT (1) from #T where Name=a.name and Id<a.id) =0

Method 7:
SELECT * from #T a where id= (select top 1 ID from #T where name=a.name an order by ID)

Method 8:
SELECT * from #T a where Id!>all (select ID from #T where Name=a.name)

Method 9 (Note: Available when ID is unique):
SELECT * from #T a where ID in (the Select min (ID) from #T Group by Name)

--SQL2005:

Method 10:
Select Id,name,memo from (select *,min (ID) over (partition by Name) as MiniD from #T a) T where Id=minid

Method 11:

Select Id,name,memo from (select *,row_number () over (partition by Name ORDER by ID) as MiniD from #T a) T where minid=1

Build Result:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1

(2 rows affected)
*/

The record with the same ID as the name, and the same as min:
Method 1:
SELECT * from #T a where NOT exists (select 1 from #T where Name=a.name and Id>a.id)

Method 2:
Select A.* from #T a join (select Max (ID) id,name from #T Group by Name) B on A.name=b.name and a.id=b.id order by ID

Method 3:
SELECT * from #T a where id= (select Max (ID) from #T where name=a.name) Order by ID

Method 4:
Select A.* from #T a join #T B on A.name=b.name and A.id<=b.id GROUP by A.id,a.name,a.memo have count (1) =1

Method 5:
SELECT * FROM #T a GROUP by Id,name,memo have id= (select Max (ID) from #T where Name=a.name)

Method 6:
SELECT * from #T a WHERE (select COUNT (1) from #T where Name=a.name and Id>a.id) =0

Method 7:
SELECT * from #T a where id= (select top 1 ID from #T where name=a.name order by ID Desc)

Method 8:
SELECT * from #T a where Id!<all (select ID from #T where Name=a.name)

Method 9 (Note: Available when ID is unique):
SELECT * from #T a where ID in (the Select Max (ID) from #T Group by Name)

--SQL2005:

Method 10:
Select Id,name,memo from (select *,max (ID) over (partition by Name) as MiniD from #T a) T where Id=minid

Method 11:
Select Id,name,memo from (select *,row_number () over (partition by Name, ORDER by ID Desc) as MiniD from #T a) T where minid=1

Build Result 2:
/*
ID Name Memo
----------- ---- ----
3 A A3
5 B B2

(2 rows affected)
*/

--2, deleting duplicate records when there is a size relationship, keep one of the records large or small

----(Roy) generates test numbers

If not object_id (' Tempdb. #T ') is null
drop table #T
Go
Create table #T ([ID] int,[name] nvarchar (1), [Memo] nvarchar (2))
Insert #T
Select 1,n ' A ', N ' A1 ' UNION ALL
Select 2,n ' A ', N ' A2 ' UNION ALL
Select 3,n ' A ', N ' A3 ' UNION ALL
Select 4,n ' B ', N ' B1 ' UNION ALL
Select 5,n ' B ', N ' B2 '
Go

----the record with the lowest ID of the same name (recommended in three-way), keep the smallest one
Method 1:
Delete A from #T a where exists (select 1 from #T where Name=a.name and Id<a.id)

Method 2:
Delete A from #T a LEFT join (select min (ID) id,name from #T Group by Name) b in A.name=b.name and a.id=b.id where b.ID is Null

Method 3:
Delete A from #T a where ID not in (the Select min (ID) from #T where Name=a.name)

Method 4 (Note: Available when ID is unique):
Delete A from #T a where ID not in (the Select min (ID) from the #T Group by Name)

Method 5:
Delete A from #T a where (select COUNT (1) from #T where Name=a.name and Id<a.id) >0

Method 6:
Delete A from #T a where id<> (the select top 1 ID from #T where name=a.name the order by ID)

Method 7:
Delete A from #T a where Id>any (select ID from #T where Name=a.name)

SELECT * FROM #T

Build Result:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1

(2 rows affected)
*/

-----name the same ID to retain the largest record:

Method 1:
Delete A from #T a where exists (select 1 from #T where Name=a.name and Id>a.id)

Method 2:
Delete A from #T a LEFT join (select Max (ID) id,name from #T Group by Name) b in A.name=b.name and a.id=b.id where b.ID is Null

Method 3:
Delete A from #T a where ID not in (the Select Max (ID) from #T where Name=a.name)

Method 4 (Note: Available when ID is unique):
Delete A from #T a where ID not in (the Select Max (ID) from the #T Group by Name)

Method 5:
Delete A from #T a where (select COUNT (1) from #T where Name=a.name and Id>a.id) >0

Method 6:
Delete A from #T a where id<> (select top 1 ID from #T where name=a.name order by ID Desc)

Method 7:
Delete A from #T a where Id<any (select ID from #T where Name=a.name)

SELECT * FROM #T
/*
ID Name Memo
----------- ---- ----
3 A A3
5 B B2

(2 rows affected)
*/

--3, deleting duplicate records without a size relationship, handling duplicate values

----(Roy) generates test numbers

If not object_id (' Tempdb. #T ') is null
drop table #T
Go
Create table #T ([Num] int,[name] nvarchar (1))
Insert #T
Select 1,n ' A ' UNION ALL
Select 1,n ' A ' UNION ALL
Select 1,n ' A ' UNION ALL
Select 2,n ' B ' UNION ALL
Select 2,n ' B '
Go

Method 1:
If object_id (' Tempdb.. # ') is not null
DROP TABLE #
Select distinct * to # from #T--Exclude duplicate record result set to generate temporary table #

TRUNCATE TABLE #T--Emptying tables

Insert #T SELECT * FROM #--Inserts temporary table # into table #t

--View Results
SELECT * FROM #T

/*
Num Name
----------- ----
1 A
1 A

(2 rows affected)
*/

--use Method 2 after re-executing test data
Method 2:

ALTER TABLE #T add ID int identity--new identity column
Go
Delete A from #T a where exists (select 1 from #T where Num=a.num and Name=a.name and id>a.id)--keep only one record
Go
ALTER TABLE #T drop column id--Delete identity column

--View Results
SELECT * FROM #T

/*
Num Name
----------- ----
1 A
1 A

(2 rows affected)

*/

--use Method 3 after re-executing test data
Method 3:
Declare roy_cursor Cursor Local for
Select COUNT (1) -1,num,name from #T GROUP by Num,name have count (1) >1
declare @con int, @Num int, @Name nvarchar (1)
Open Roy_cursor
FETCH NEXT from Roy_cursor to @con, @Num, @Name
While @ @Fetch_status =0
Begin
SET ROWCOUNT @con;
Delete #T where [email protected] and [email protected]
SET ROWCOUNT 0;
FETCH NEXT from Roy_cursor to @con, @Num, @Name
End
Close Roy_cursor
Deallocate roy_cursor

--View Results
SELECT * FROM #T
/*
Num Name
----------- ----
2 b
1 A

(2 rows affected)
*/

SQL Duplicate data displays only one