/*
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 www.111Cn.net ')
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. Randomly retrieve a data entry by name group www.111cn.net.
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
*/ |
