SQL Group One (or several) records per group (rank)

MySQL Group takes the first few records of each group (rank) with group by and order by

Http://www.jb51.net/article/31590.htm

--group The data of the row with the maximum (small) value by a field

The code is as follows:

/**/

--Create the table and insert the data:

The code is as follows:

Create TableTB (namevarchar(Ten), Valint, Memovarchar( -)) 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') Insert  intoTbValues('b',5,'b5b5b5b5b5') Go 

--One, group by name takes the data of the row with the largest Val value.

The code is as follows:

--method 1:select a.* from terabyte a WHERE val = (select Max (val) from TB where name = A.name) Order by A.name--Method 2:SelectA.*  fromTB Awhere  not exists(Select 1  fromTbwhereName=A.name andVal>a.val)--Method 3: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--Method 5SelectA.*  fromTB Awhere 1 >(Select Count(*) fromTbwhereName=A.name andVal>A.val)Order  byA.name/*name Val Memo-----------------------------------------a 3 a3:a of the third value B 5 b5b5b5b5b5*/ 

I recommend the use of 1,3,4, the results show that 1,3,4 efficiency is the same, 2,5 efficiency is poor, but I 3,4 the same efficiency no doubt, 1 is not the same, want to do.

---second, group by name to take the data of the row with the lowest Val value.

The code is as follows:

--method 1:select a.* from terabyte a WHERE val = (select min (val) from TB where name = A.name) Order by A.name--Method 2:SelectA.*  fromTB Awhere  not exists(Select 1  fromTbwhereName=A.name andVal<a.val)--Method 3:SelectA.*  fromTB A, (SelectNamemin(Val) Val fromTbGroup  byName) bwhereA.name=B.name andA.val=B.valOrder  byA.name--Method 4:SelectA.*  fromTB AInner Join(SelectName,min(Val) Val fromTbGroup  byName) b onA.name=B.name andA.val=B.valOrder  byA.name--Method 5SelectA.*  fromTB Awhere 1 >(Select Count(*) fromTbwhereName=A.name andVal<A.val)Order  byA.name/*name Val Memo-----------------------------------------a 1 a1--a first value B 1 b1--b first value*/

--Three, by name Group to take the first occurrence of the row data.

The code is as follows:

Select A.*fromwhere= (selecttop1from  where=orderby/** *

--Four, according to the name group randomly fetch a piece of data.

The code is as follows:

Select A.*fromwhere= (selecttop1from  where=orderbynewidorder by A.name/** *

--Five, by name group to take the smallest two (n) Val

The code is as follows:

SelectA.*  fromTB Awhere 2 >(Select Count(*) fromTbwhereName=A.name andVal<A.val)Order  byA.name,a.valselect A.*  fromTB AwhereValinch(Select Top 2Val fromTbwhereName=A.nameOrder  byValOrder  byA.name,a.valSelectA.*  fromTB Awhere exists(Select Count(*) fromTbwhereName=A.name andVal<A.val having Count(*)< 2)Order  byA.name/*name Val Memo-----------------------------------------a 1 a1--a first value a 2 A2 (second value of a) B 1 b1--b first value B 2 b2b2b2b2 */ 

--Six, by name Group to take the largest two (n) Val

The code is as follows:

SelectA.*  fromTB Awhere 2 >(Select Count(*) fromTbwhereName=A.name andVal>A.val)Order  byA.name,a.valSelectA.*  fromTB AwhereValinch(Select Top 2Val fromTbwhereName=A.nameOrder  byValdesc)Order  byA.name,a.valSelectA.*  fromTB Awhere exists(Select Count(*) fromTbwhereName=A.name andVal>A.val having Count(*)< 2)Order  byA.name/*name Val Memo-----------------------------------------a 2 A2 (second value of a) a 3 a3:a the third value B 4 b4b4 b 5 b5b5b5b5b5 */ 

-Seven, if the entire row of data is duplicated, all columns are the same (for example, 5th in the table below, 62 rows of data are identical).
Group by name to fetch the largest two (n) Val

The code is as follows:

/**/

SQL Group One (or several) records per group (rank)