Group by in MYSQL performs grouping query and sorting

After group by is used, the queried list is sorted by the default asc sequence of the primary key. order by can only be used for the grouped list because the priority of order by is lower than that of group, so how can we solve the grouping Sorting Problem? Let's take a look at the specific operation methods.

So how can we sort the grouped data by the desc of the primary key?

There are two solutions:

1. Use a subquery to sort the table and then perform group by queries on the Word Table. The column obtained in this query will be the column of a record obtained from the subquery.
2. Use the max function to compare and query the columns that can calculate the maximum value. This method only outputs the comparison columns according to desc without affecting other columns.

The results of the above two methods are different, and they must be used as needed.

Example:

Table tab1 has three columns: primary key id, name, and logon time.
Id name time
1 a01 1
2 www. bKjia. c0m 2
3 a03 3
4 a02 4
5 a01 5
6 a04 6

Requirements:

1. query the logon times and the last logon time of each user.

Solution:

Method 1
Select sub. name, count (sub. id) as login_nums, sub. time as last_time
From (select * from tab1 order by time) sub
Group by sub. name
Note: Each column obtained by the query belongs to the same row record of the original table. If the query id is specified, the id is also the id bKjia. c0m of the row record at the latest time.

Method 2

Select name, count (id) as login_nums, max (time) as last_time
From tab1
Group by name
Note that each column obtained by the query no longer belongs to the same row record of the original table.