Removing duplicate rows from SQL statement techniques

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 the same, part is not the same), So how do we do that? Through years of experience in compiling databases, I have compiled the following methods for your reference and use.

Method 1: Apply to return fewer fields

Select F1,f2,f3,MAX (F4) from TABLENAME GROUP by f1,f2,f3

Method 2: Apply to all fields of the return row, you need to specify a different field

SELECT * from TABLENAME T where F4= (select MAX (F4) from TABLENAME where T.f1=f1 and T.f2=f2 and T.F3=F3)

Method 3: Applies to all fields that return rows, you need to specify a different field "find the rows to be removed"

Select t1.* from TABLENAME as T1, (select F1,f2 , MAX (F3)As F3 from TABLENAME GROUP by F1,F2 have COUNT (*) >1) as T2where t1.f3<t2.f3and T1. F1=t2. F1 and T1. F2=t2. F2

Original address: http://www.zuowenjun.cn/post/2014/08/02/10.html