De-Redo operations in SQL

ifNot OBJECT_ID ('Tempdb: #T') is NULLdrop table #TGoCreate table #T ([ID]int, [Name] nvarchar (1), [Memo] nvarchar (2) ) Insert #TSelect 1N'A'N'A1'Union AllSelect 2N'A'N'A2'Union AllSelect 3N'A'N'A3'Union AllSelect 4N'B'N'B1'Union AllSelect 5N'B'N'B2'Go----the record with the lowest ID of the same name (1 recommended,2,3), Method 3 at SQL05, the efficiency is higher than 1,2Method 1:select* from#T AwhereNot EXISTS (Select 1  from#TwhereName=a.name and id<a.id) Method 2:SelectA.* from#T a Join (SelectMin (ID) id,name from#T GROUP by Name) B on A.name=b.name and a.id=b.ID Method 3:Select* from#T AwhereId= (SelectMin (ID) from#TwhereName=a.name) Method 4:SelectA.* 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)=1Method 5:Select* from#T a GROUP by Id,name,memo have id= (SelectMin (ID) from#TwhereName=A.name) Method 6:Select* from#T Awhere(SelectCount1) from#TwhereName=a.name and id<a.id) =0Method 7:Select* from#T AwhereId= (SelectTop1Id from#TwhereName=a.name Order by ID) Method 8:Select* from#T AwhereId!>all (SelectId from#TwhereName=A.name) Method 9 (note: Available when ID is unique):Select* from#T AwhereIdinch(SelectMin (ID) from#T GROUP by Name)--SQL2005: Method:SelectId,name,memo from(Select*,min (ID) over (partition by Name) asMiniD from#T a) TwhereId=MiniD methods:SelectId,name,memo from(Select*,row_number () over (partition by Name, order by ID) asMiniD from#T a) TwhereMinid=1Build Result:/*ID Name Memo-------------------1 A A14 B B1 (2 rows affected)*/--II, name the record with the same ID Max, opposite to min: Method 1:select* from#T AwhereNot EXISTS (Select 1  from#TwhereName=a.name and Id>a.id) Method 2:SelectA.* from#T a Join (SelectMax (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 AwhereId= (SelectMax (ID) from#TwhereName=a.name) Order by ID Method 4:SelectA.* 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)=1Method 5:Select* from#T a GROUP by Id,name,memo have id= (SelectMax (ID) from#TwhereName=A.name) Method 6:Select* from#T Awhere(SelectCount1) from#TwhereName=a.name and id>a.id) =0Method 7:Select* from#T AwhereId= (SelectTop1Id from#TwhereName=a.name ORDER BY ID Desc) Method 8:Select* from#T AwhereId!<all (SelectId from#TwhereName=A.name) Method 9 (note: Available when ID is unique):Select* from#T AwhereIdinch(SelectMax (ID) from#T GROUP by Name)--SQL2005: Method:SelectId,name,memo from(Select*,max (ID) over (partition by Name) asMiniD from#T a) TwhereId=MiniD methods:SelectId,name,memo from(Select*,row_number () over (partition by Name, ORDER by ID Desc) asMiniD from#T a) TwhereMinid=1Build result 2:/*ID Name Memo-------------------3 A A35 B B2 (2 rows affected)*/if it is not possible to solve the problem of the proposed landlord to give specific needs is based on which field to repeat

This is the information I found in the CSDN forum, in the original: http://bbs.csdn.net/topics/390315926

The above is on the 2 floor.

De-Redo operations in SQL