"Go" SQL Server gets one of the most valued data in each category

1 /*2 The data are as follows:3 name Val Memo4 a 2 A2 (second value of a)5 the first value of a 1 a1--a6 the third value of a 3 a3:a7 the first value of the B1--b B 18 the third value of B3:b B 39 B 2 b2b2b2b2Ten B 4 B4b4 One B 5 B5b5b5b5b5 A */ - --To create a table and insert data: - Create TableTB (namevarchar(Ten), Valint, Memovarchar( -)) the Insert  intoTbValues('a',2,'A2 (second value of a)') - Insert  intoTbValues('a',1,'the first value of a a1--a') - Insert  intoTbValues('a',3,'the third value of the A3:a') - Insert  intoTbValues('b',1,'the first value of a b1--b') + Insert  intoTbValues('b',3,'the third value of the B3:b') - Insert  intoTbValues('b',2,'b2b2b2b2') + Insert  intoTbValues('b',4,'b4b4') A Insert  intoTbValues('b',5,'b5b5b5b5b5') at Go -     - --first, the data in the row with the largest value of Val is grouped by name.  - --Method 1: - SelectA.*  fromTB AwhereVal=(Select Max(Val) fromTbwhereName=A.name)Order  byA.name - --Method 2: in SelectA.*  fromTB Awhere  not exists(Select 1  fromTbwhereName=A.name andVal>a.val) - --Method 3: to SelectA.*  fromTB A, (SelectNameMax(Val) Val fromTbGroup  byName) bwhereA.name=B.name andA.val=B.valOrder  byA.name + --Method 4: - SelectA.*  fromTB AInner Join(SelectName,Max(Val) Val fromTbGroup  byName) b onA.name=B.name andA.val=B.valOrder  byA.name the --Method 5 * SelectA.*  fromTB Awhere 1 >(Select Count(*) fromTbwhereName=A.name andVal>A.val)Order  byA.name $ /*Panax Notoginseng name Val Memo - ---------- ----------- --------------------  the the third value of a 3 a3:a + B 5 B5b5b5b5b5 A */ the   +   　　 

Notation 6

SELECT *
From (
SELECT *,
Row_number () over (PARTITION by name ORDER by Val DESC) RIDs
From TB
) as T
WHERE RID = 1

If there is more than two of the same Val in a name group above, query method 1-5 is incorrect. However, method 1-5 can be re-processed

For example:

INSERT into TB values (' A ', 2, ' A2 (second value of a) ')
INSERT into TB values (' A ', 3, ' A1--a's first value ')
INSERT into TB values (' A ', 3, ' A3:a's third value ')

Transferred from: http://www.cnblogs.com/zfanlong1314/p/3393946.html

"Go" SQL Server gets one of the most valued data in each category