I have table like this (Using SQL Server) :


TempTbl:

ID Name Val_1 Val_2
===============================
1 A 10 adf
2 B 12 dasfasd
3 C 5 fgsdfg
4 A 21 dsfg
5 C 13 sdbs



I want to result a table showing all col with max value in Val_1 for each Name. i.e.

ID Name Val_1 Val_2
===============================
2 B 12 dasfasd
4 A 21 dsfg
5 C 13 sdbs



I can only think of this SQL:

SELECT * FROM TempTbl t1, (SELECT Name, max(val_1) FROM TempTbl Group By Name) t2
WHERE t1.Name = t2.Name AND t1.Val_1 = t2.Val_1



I would like to ask is there any better way for searching this result rather than
joinning the itself twice (becuase the TempTbl is very big).


Many Thanks.!!!!