Query multiple fields, sort by a field

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)
*/

If it is not possible to solve the problem of the proposed landlord to give specific needs is based on which field to repeat

Query multiple fields, sort by a field