Original: SQL Server gets the highest value of data in each category
/* Data as follows: Name Val Memo a 2 A2 (second value of a) a 1 a1--a first value a 3 a3:a third value B 1 b1--b first value B 3 b3:b third value B 2 b2b2b2b2 B 4 b4b4 B 5 B5B5B5B5B5 * *--CREATE TABLE and insert data: Creation table TB (name varchar), Val int,memo varchar (20 ) insert into TB values (' A ', 2, ' A2 (second value of a) ') insert into TB values (' A ', 1, ' A1--a's first value ') insert into TB values (' A ', 3, ' the third value of a3:a ') insert into TB values (' B ', 1, ' first value of ' b1--b ') insert into TB values (' B ', 3, ' B3:b's third value ') INSERT into TB values (' B ', 2, ' b2b2b2b2 ') inserts into terabytes values (' B ', 4, ' b4b4 ') insert into TB values (' B ', 5, ' b5b5b5b5b5 ') go--First, group by name to take the data of the row with the largest Val value. --Method 1:select a.* from TB a WHERE val = (select Max (val) from TB where name = A.name) Order by a.name--method 2:select A.* fro M TB a Where NOT exists (select 1 from TB where name = A.name and val > A.val)--method 3:select a.* from TB A, (select name, Max (Val) Val from TB Group by name) b where a.name = B.name and A.val = b.val ORDER by a.name--Method 4:select a.* from TB a inner joins (select name, Max (Val) Val from TB Group by name) b on a.name = B.name and A.val = b . val Order by a.name--method 5 Select A.* from TB a where 1 > (select COUNT (*) from TB where name = A.name and val > A.va L) Order by a.name/* name Val Memo-----------------------------------------A 3 A3:a's third value B 5 B5B5B5B5B5 */
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.
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 ')
SQL Server gets one of the most valued data in each category