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:

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

The code is as follows:

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 TB a WHERE val = (select min (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, Min (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 JOIN (select name, Min (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.val) Order by A.name/* nam E 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:

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

The code is as follows:

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

The code is as follows:

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

The code is as follows:

-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)