remove duplicate rows in sql

SQL Server deduplication is one of our most common actions, and here's a look at six different ways SQL Server can remove duplicate rows for your reference.1. If there is an ID field, it is a unique fielddelect table TableName where ID not in (the Select Max (ID) from the ta

To remove duplicate row data in the table, you may immediately think of the disintct keyword, but disintct can only be used to remove all columns in the table is the same row, if you encounter the need to remove multiple fields in the table repeating rows (that is, part of t

Mysql complex SQL statements (query and delete duplicate rows), mysqlsql 1. Find duplicate rows SELECT * FROM blog_user_relation a WHERE (a.account_instance_id,a.follow_account_instance_id) IN (SELECT account_instance_id,follow_account_instance_id FROM blog_user_relation GRO

For repeated row deletion problems, it is difficult to find a suitable answer on the Internet and there are a lot of questions, but there is no solution to the previous record in the search engine. In fact, this problem can be effectively solved. 1. If the table does not have a primary key (or the same row does not have different content columns), you need to create an auto-increment column to distinguish different columns. For example Copy codeThe Code is as follows: alter table [tablename]

For repeated row deletion problems, it is difficult to find a suitable answer on the Internet and there are a lot of questions, but there is no solution to the previous record in the search engine.In fact, this problem can be effectively solved.1. If the table does not have a primary key (or the same row does not have different content columns), you need to create an auto-increment column to distinguish different columns. For exampleCopy codeThe Code is as follows:Alter table [tablename] add [TI

to judge repetition. For example, if only col1 is used, if the content of col1 is the same, the record is the same. 5.Copy codeThe Code is as follows: select identity (int, 1, 1) as id, * into # temp from tabelSelect * from # temp where id in (Select max (id) from # emp where having count (*)> 1 group by col1, col2, col3 ...) 6.Copy codeThe Code is as follows: select distinct * into # temp from tablenameDelete tablenameGoInsert tablename select * from # temp SqlclubGoDrop table # temp The prece

Recently, I have been sorting out the database of my website. The database is mysql. Because of the increasing access function, I imported access and reviewed the SQL. There are a lot of data, 0.15 million records, about 1 GB. The two fields a and B of some data are repeated, so that duplicate rows can be considered as duplicated data, as long as any row is retai

In a table, some fields are not primary keys and are not unique, but in the process of use, the field does not apply duplicates, and sometimes it repeats. So, to find the row for a repeating field, use the SQL statement:--That is:--select * from table Where repeating field in (Select repeating field from table Group by repeating field having Count (*) >1)--to a specific example Select * from T_exte RM where Mainkeyindex in (select Mainkeyindex from T_

1. If an ID field exists, it is a unique field.Copy codeThe Code is as follows:Delect table where id not in (Select max (id) from table group by col1, col2, col3...) The field followed by the group by clause is the condition for you to judge repetition. For example, if only col1 is used, if the content of col1 is the same, the record is the same.2. This can also be used to determine all fields.Copy codeThe Code is as follows:Select * into # aa from table group by id1, id2 ,....Delete tableInsert

Where has count (*) >1 GROUP BY Col1,col2,col3,col4 ) The field followed by the GROUP BY clause is the condition that you use to determine the repetition, such as only col1, so as long as the content of the Col1 field is the same as that of the record. 5. Copy Code code as follows: Select Identity (int,1,1) as id,* into #temp from tabel SELECT * from #temp where ID in ( Select Max (ID) from #emp where has count (*) >1 GROUP by col1,col2,col3 ...) 6.

Id Email UserName1 [email protected] Taiseerjoudeh2 [Email protected]il.com Hasanahmad3 [email protected] Moatasemahmad4 [email protected] Salmatamer5 [email protected] Ahmadradi6 [email protected] BillGates7 [email protected] shareefkhaled8 [email protected] Aramnaser9 [email protected] Laylaibrahim[Email protected] Remaoday[Email protected] Fikrihusein[Email protected] Zakarihusein[Email protected] Laylaibrahim[Email protected] Tamerwesam[Email protected] Khaledhasaan[Email protected] Asaadibr

t_employee order by fsalary DESC)Order by fsalary DESC The simplified implementation of the row_number function is added after sqlserver2005. Remove data duplication (distinct) execute the SQL statement in the remarks, alter and insert is executed separately. select fdepartment from t_employee select distinct fdepartment from

Tags: blog http sp c log r AD BSWith such a problem, a SQL statement is used to query the names of students who have more than 80 points per course.Here is the tableAnalysis, query each course is more than 80 students. SELECT DISTINCT name from dbo.student WHERE fenshuStatements less than or equal to 80 select name from Dbo.student WHERE fenshuThis is repeated, and it is worthwhile to use the words in the not-in (no-time) statement to spell the two se

The distinct keyword can be used to remove duplicate rows from the results of the SELECT statement. If noDistinct: All rows, including duplicate rows, are returned. For example, if you select all author IDs in titleauthorDistinct,

') if exists (SELECT * from tempdb. sysobjects where id=object_id (' tempdb. #Tmp1 ')) drop table #Tmp1Select ID as autoid, * into #Tmp1 from adminif exists (SELECT * from tempdb. sysobjects where id=object_id (' tempdb. #Tmp2 ')) drop table #Tmp2Select min (autoid) as autoid into #Tmp2 from #Tmp1 Group by Username,passwordif exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' admin ') and objectproperty (Id,n ' isusertable ') = 1) Drop tabl E adminSelect Id,username,password to admi

Turn from Hibiscus bloghttp://blog.sina.com.cn/liurongxiu1211 SQL to remove duplicate statements(2012-06-15 15:00:01)SQL single table/multi-table query removes duplicate recordsSingle table DistinctMulti-table GROUP byGroup by must be placed before order by and limit, or it

be unique for both fieldsSelect Identity(int,1,1) asAutoid,* into#Tmp fromTableNameSelect min(autoid) asAutoid into#Tmp2 from#TmpGroup byname,autoidSelect * from#TmpwhereAutoidinch(SelectAutoid from#tmp2)The last select is the result set that name,address not duplicate (but one more autoid field, which can be written in the SELECT clause without this column in the actual write)FourDuplicate querySelect * from where inch (Selectfromgroupby hascoun

columns are added to resolve.2, this kind of repetition problem usually requires to keep the first record in the duplicate record, the operation method is as followsSuppose there is a duplicate field name,address, which requires the result set to be unique for both fieldsSelect Identity (int,1,1) as Autoid, * into #Tmp from TableNameSelect min (autoid) as autoid into #Tmp2 from #Tmp Group by name,autoidSEL

Select Top 12 ID, URL, titleorname from t_userscolumn A where Mark = '1' and not exists (select * From t_userscolumn where url =. URL and titleorname =. titleorname and Mark = '1' and ID>. ID) order by ID DESC ========================================================== ======== Remove duplicate values from SQL Server 2000 the table structure is zymc -- major

row_num > 1Method two to remove the weight according to a single condition:SQL code Delete From table where primary key ID not in ( Select Max(primary key ID) from table group by need to go to heavy fields having count (need to go to the heavy field) >=1 ) [SQL]View Plaincopy Delete from table where primary key ID not in ( Select Max (pri