Mysql DATA group obtains all the largest record rows of a field value, mysql Field

Mysql DATA group obtains all the largest record rows of a field value, mysql Field

Requirements:

The same uid (User) in the table has multiple game level records. Now we need to retrieve the data of the highest level of all users, and the earlier the time. This is a typical table

+ ------ + ------- + -------------- + ------------------- + | Uid | level | role | time | + ------ + ------- + -------------- + ------------------- + | 7 | 1 | rock magic | 15:01:05 | 1134 | 4 | sole respect | 15:02:38 | 1134 | 4 | sole respect | 15:02:39 | 7 | 3 | rock magic | 15:02:59 | 5 | 3 | Yun 'er Ling♦| 15:04:09 | 7363 | 6 | Shi he bingyue | 15:04:23 | 6684 | 4 | Long way to go empty | 15:05:13 | 7 | 16 | rock magic | 15:05:46 | | 1 | 2 | doutuhao | 15:05:48 | 7 | 26 | rock and roll magic | 15:08:36 | 6684 | 8 | Wei haogao Wei | 15:08:45 | 5 | 12 | yun 'er Ling♦| 15:09:47 | 6834 | 1 | invincible wing pursuit | 15:10:56 | 5 | 16 | Yun er Ling♦| May 15:11:18 | 8719 | 1 | jun wangchi | 15:11:48 | 6274 | 36 | solitary | 15:12:22 | 8724 | 26 | Tong | 15:12:31 | 1134 | 32 | sole respect | 15:12:51 | 7 | 26 | rock magic | 15:13:38 | 5 | 25 | Yun er Ling♦| 15:14:48 | 7757 | 3 | Yi Qingling Xuan | 15:16:50 | 7 | 26 | rock magic | 15:17:26 | 5 | 28 | Yun 'er Ling♦| May 15:18:08 | 7757 | 23 | qingqingling Xuan | 15:19:29 | 6274 | 43 | solitary | 15:19:54 | 8724 | 30 | Tongtong | 15:20:39 | 7757 | | 26 | Qingling spin | 15:20:58 | 8707 | 36 | dry | 15:22:28 | 7757 | 29 | Qingling spin | 15:23:05 | 7757 | 32 | yi Qingling Xuan | 15:24:57 | 8726 | 10 | Lian Xi | 15:26:01 | 7363 | 34 | Shi he bingyue | 15:26:58 | 7 | 26 | rock magic | 15:27:33 | 5 | 37 | Yun 'er Ling♦| May 15:27:37 | 8347 | 1 | invincible | 15:28:09 | 6274 | 47 | 1134 alone | 15:28:13 | 1 | 32 | doutuhao | 15:29:18 | 46 | only my only one | May 15:30:52 | 7757 | 41 | qingqingqingling Xuan | 15:30:56 | 9 | 34 | meal tips | 15:31:03 | 6274 | 48 | alone | 15:31:18 | 8724 | 41 | Tong | 15:32:30 | 7757 | 42 | Yi Qingling Xuan | 15:34:24 | 1134 | 48 | only my unique respect | 15:34:56 | 1100 | 2 | san magic Xiao | 15:35:54 | 1008 | 21 | Dudu ice rolling | 15:36:10 | 7757 | 45 | Yi Qingling spin rolling | 15:40:49 | 3088 | 4 | war soul☼Lingkong | 15:41:38 | 5 | 41 | Yun 'er Ling♦| 15:41:56 | 7757 | 46 | qingqingling Xuan | 15:43:24 |

 

Implementation process:

1. Sort the data twice first, and sort the uid and level in descending order.

2. Grouping the sorted results by uid. grouping results is sorted in descending order and time is sorted in ascending order.

 

Implement SQL:

 

Select * from (select. uid,. level,. role,. time from data table a where order BY. uid desc,. level desc) as Alias group by user_uid order by level desc, time asc limit 30;

In PHP code, how can I query the MYSQL database to obtain the ID number of the largest record in a field in the data table? For example: See Question supplement.

I did the following test:
Create table music (
Id varchar (10 ),
Title varchar (100 ),
Name varchar (10)
);
Insert into music values ('2', 'parting ', '123 ');
Insert into music values ('15', 'friends', '123 ');
Insert into music values ('20160301', 'farewell ', '20160301 ');

<? Php
$ Conn = mysql_connect ("localhost: 3307", "database username", "Database Password ");
Mysql_select_db ("date ");
$ Result = mysql_query ("select max (id + 0) max_id from music", $ conn );
$ Field = mysql_fetch_row ($ result );
Print_r ($ field );
?>
Result: Array ([0] => 161)
Because mysql varchar does not use max (), id + 0 is used to convert the id type to solve this problem. If the id is an integer type during table creation, you can directly use max (). For more information, see hb.qq.com/a/20151124/41061.htm.

All columns of the row with the maximum field value in each group after the SQL query Group

Create table # a (address char (2), fenshu varchar (8 ))
Insert into # a values ('a1', 's1 ')
Insert into # a values ('a1', 's2 ')
Insert into # a values ('a2 ', 's3 ')
Insert into # a values ('a1', 's7 ')
Insert into # a values ('a2 ', 's4 ')
Insert into # a values ('a2 ', 'ss ')
Insert into # a values ('a1', 'ss ')
Insert into # a values ('a2 ', 's9 ')

In the actual syntax test, grouping conditions should be added, otherwise duplicate records will be retrieved (a. address = B. address)
Select * from #
Where fenshu in (
Select max (fenshu) from # a B where a. address = B. address
Group by address)