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