An answer to a question about distinct

Answer | Questions often have friends ask questions like this,
The data in the table is as follows
ID AA BB
1 123 456
1 4535 54
1 60 6564
1 60 656
2 50 664
2 60 6
3 89 89
4 40 4242
Hope to get the result is
ID AA BB
1 123 456
2 50 664
3 89 89
4 40 4242
Of course, the environment is SQL Server
The answers and analysis are as follows:
1, many friends want to use distinct to solve the problem, but it is not possible, DISCTINCT will repeat the record ignored,
But it ignores the exact same duplicate record, not the duplicate record of one of the fields, so there is only
Such a grammar
Select distinct ID,AA,BB from Tname
Other such as SELECT DISTINCT (ID), aa,bb from Tname or
The AA,BB of the select Id,distinct is invalid.
2, using the group by and aggregate functions
Select Id,max (AA) as Aa,max (BB) as BB from Tname GROUP by ID
You can get the following results
ID AA BB
1 4535 6564
2 60 664
3 89 89
4 40 4242
ID is unique, but not necessarily the following field is the same record
3, using temporary tables
Select IDENTITY (int,1,1) as tid,id,aa,bb into #Tmp from Tname
Select T1.id,t1. Aa,t1. BB from #Tmp t1 where T1. TID in
(select min (T2). TID) from #Tmp T2 Group by T2.id)
So you can get the results that meet your requirements.
But with two T-SQL statements,
And if it's a large amount of data, the performance problem will be significant.
So far, I haven't found a way to implement the same functionality with a T-SQL statement,
If anyone has, want to add