SQL Server groups the data of the row with the maximum (small) value by a Field

/*
The data is as follows:
Name Val memo
A 2 A2 (the second value of)
A 1 A1 -- the first value of
A 3 A3: The third value of
B 1 B1 -- the first value of B
B 3 B3: The third value of B
B 2 b2b2b2b2
B 4 b4b4
B 5 b5b5b5b5b5
*/
-- Create a table and insert data:
Create Table Tb (name varchar (10), Val int, memo varchar (20 ))
Insert into TB values ('A', 2, 'a2 (second value of )')
Insert into TB values ('A', 1, 'a1 -- the first value of ')
Insert into TB values ('A', 3, 'a3: The third value of ')
Insert into TB values ('B', 1, 'b1 -- the first value of B ')
Insert into TB values ('B', 3, 'b3: The third value of B ')
Insert into TB values ('B', 2, 'b2b2b2b2 ')
Insert into TB values ('B', 4, 'b4b4 ')
Insert into TB values ('B', 5, 'b5b5b5b5 ')
Go

-- 1. Group data by name to get 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. * from tb a where not exists (select 1 from TB where name = A. Name and Val> A. Val)
-- Method 3:
Select. * from tb a, (Select name, max (VAL) Val from TB group by name) B where. name = B. name and. val = B. val order by. name
-- Method 4:
Select. * from TB a inner join (Select name, max (VAL) Val from TB group by name) B on. name = B. name and. val = B. val order by. name
-- Method 5
Select a. * from tb a where 1> (select count (*) from TB where name = A. Name and Val> A. Val) order by A. Name
/*
Name Val memo
-----------------------------------------
A 3 A3: The third value of
B 5 b5b5b5b5b5
*/

-- 2. group the data of the row with the smallest Val value by name.
-- Method 1:
Select a. * from tb a where val = (select Min (VAL) from TB where name = A. Name) order by A. Name
-- Method 2:
Select a. * from tb a where not exists (select 1 from TB where name = A. Name and Val <A. Val)
-- Method 3:
Select. * from tb a, (Select name, min (VAL) Val from TB group by name) B where. name = B. name and. val = B. val order by. name
-- Method 4:
Select. * from TB a inner join (Select name, min (VAL) Val from TB group by name) B on. name = B. name and. val = B. val order by. name
-- Method 5
Select a. * from tb a where 1> (select count (*) from TB where name = A. Name and Val <A. Val) order by A. Name
/*
Name Val memo
-----------------------------------------
A 1 A1 -- the first value of
B 1 B1 -- the first value of B
*/

-- 3. Group by name to obtain the data of the row that appears for the first time.
Select a. * from tb a where val = (select top 1 Val from TB where name = A. Name) order by A. Name
/*
Name Val memo
-----------------------------------------
A 2 A2 (the second value of)
B 1 B1 -- the first value of B
*/

-- 4. A random data entry is grouped by name.
Select a. * from tb a where val = (select top 1 Val from TB where name = A. Name order by newid () Order by A. Name
/*
Name Val memo
-----------------------------------------
A 1 A1 -- the first value of
B 5 b5b5b5b5b5
*/

-- 5. Group by name to get the smallest two (n) Val values
Select a. * from tb a where 2> (select count (*) from TB where name = A. Name and Val <A. Val) order by A. Name, A. Val
Select a. * from tb a where Val in (select Top 2 Val from TB where name = A. Name order by Val) order by A. Name, A. Val
Select. * from tb a where exists (select count (*) from TB where name =. name and Val <. val having count (*) <2) Order by. name
/*
Name Val memo
-----------------------------------------
A 1 A1 -- the first value of
A 2 A2 (the second value of)
B 1 B1 -- the first value of B
B 2 b2b2b2b2
*/

-- 6. Group by name to get the maximum two (n) Val values
Select a. * from tb a where 2> (select count (*) from TB where name = A. Name and Val> A. Val) order by A. Name, A. Val
Select a. * from tb a where Val in (select Top 2 Val from TB where name = A. Name order by Val DESC) order by A. Name, A. Val
Select. * from tb a where exists (select count (*) from TB where name =. name and Val>. val having count (*) <2) Order by. name
/*
Name Val memo
-----------------------------------------
A 2 A2 (the second value of)
A 3 A3: The third value of
B 4 b4b4
B 5 b5b5b5b5b5
*/
-- 7. If the data in the entire row is repeated, all columns are the same.
/*
The data is as follows:
Name Val memo
A 2 A2 (the second value of)
A 1 A1 -- the first value of
A 1 A1 -- the first value of
A 3 A3: The third value of
A 3 A3: The third value of
B 1 B1 -- the first value of B
B 3 B3: The third value of B
B 2 b2b2b2b2
B 4 b4b4
B 5 b5b5b5b5b5
*/
-- Only one temporary table can be used in SQL Server 2000 to generate an auto-incrementing column. The maximum or minimum values of Val are obtained first, and then the data is obtained through the auto-incrementing column.
-- Create a table and insert data:
Create Table Tb (name varchar (10), Val int, memo varchar (20 ))
Insert into TB values ('A', 2, 'a2 (second value of )')
Insert into TB values ('A', 1, 'a1 -- the first value of ')
Insert into TB values ('A', 1, 'a1 -- the first value of ')
Insert into TB values ('A', 3, 'a3: The third value of ')
Insert into TB values ('A', 3, 'a3: The third value of ')
Insert into TB values ('B', 1, 'b1 -- the first value of B ')
Insert into TB values ('B', 3, 'b3: The third value of B ')
Insert into TB values ('B', 2, 'b2b2b2b2 ')
Insert into TB values ('B', 4, 'b4b4 ')
Insert into TB values ('B', 5, 'b5b5b5b5 ')
Go

Select *, PX = identity (INT, 1, 1) into TMP from TB

Select M. Name, M. Val, M. memo from
(
Select T. * from TMP t where val = (select Min (VAL) from TMP where name = T. Name)
) M where PX = (select Min (PX) from
(
Select T. * from TMP t where val = (select Min (VAL) from TMP where name = T. Name)
) N where N. Name = M. Name)

Drop table TB, TMP

/*
name Val memo
---------- ----------- ------------------
A 1 A1 -- the first value of A
B 1 B1 -- the first value of B value

(two rows affected)
*/
-- you can use the row_number function in SQL Server 2005 without using a temporary table.
-- create a table and insert data:
Create Table Tb (name varchar (10), Val int, memo varchar (20 ))
insert into TB values ('A', 2, 'a2 (second value of A) ')
insert into TB values ('A', 1, 'a1 -- the first value of a ')
insert into TB values ('A', 1, 'a1 -- the first value of ')
insert into TB values ('A', 3, 'a3: The third value of a ')
insert into TB values ('A', 3, 'a3: The third value of a ')
insert into TB values (' B ', 1, 'b1 -- the first value of B ')
insert into TB values ('B', 3, 'b3: The third value of B ')
insert into TB values (' B ', 2, 'b2b2b2b2')
insert into TB values ('B', 4, 'b4b4 ')
insert into TB values (' B ', 5, 'b5b5b5b5b5 ')
go

Select M. Name, M. Val, M. memo from
(
Select *, PX = row_number () over (order by name, Val) from TB
) M where PX = (select Min (PX) from
(
Select *, PX = row_number () over (order by name, Val) from TB
) N where N. Name = M. Name)

Drop table TB

/*
Name Val memo
-----------------------------------------
A 1 A1 -- the first value of
B 1 B1 -- the first value of B

(2 rows affected)
*/