In most applications, the group concat function is usually used for row column conversion. In fact, the group concat function also has a very important function, that is, sorting within groups.

Group ABCD concat full syntax:

Group concat ([distinct] field to connect [Order BY ASC/DESC sort field] [Separator 'Separator'])

1. Basic usage:

1) In general, use group? Concat() for column row conversion:

mysql> selectfid,name from test;

+-----+------+

| fid | name |

+-----+------+

|   1 | a   |

|   1 | b   |

|   1 | b   |

|   2 | A   |

|   2 | Z   |

+-----+------+

mysql> selectfid,group_concat(name) from test group by fid;

+-----+--------------------+

| fid |group_concat(name) |

+-----+--------------------+

|   1 | a,b,b              |

|   2 | A,Z                |

+-----+--------------------+

2) The group ABCD concat() function supports specifying separators. The default is comma. You can use SEPARATOR appoint;

selectfid,group_concat(name separator ';') from test group by fid;

+-----+----------------------------------+

| fid |group_concat(name separator ';') |

+-----+----------------------------------+

|   1 | a;b;b                            |

|   2 | A;Z                              |

+-----+----------------------------------+

3) The group ABCD concat() function supports sorting. By default, when the column is converted to a row through the group ABCD concat() function, the ORDER is uncertain. You can use ORDER BY designation;

selectfid,group_concat(name) from test group by fid;

+-----+--------------------+

| fid |group_concat(name) |

+-----+--------------------+

|   1 | a,b,b              |

|   2 | A,Z,C              |

+-----+--------------------+

mysql> selectfid,group_concat(name order by name desc) from test group by fid;

+-----+---------------------------------------+

| fid |group_concat(name order by name desc) |

+-----+---------------------------------------+

|   1 | b,b,a                                 |

|   2 | Z,C,A                                 |

+-----+---------------------------------------+

4) GROUP_CONCAT() function supports de duplication function, which can be realized by distinct keyword;

mysql> selectfid,group_concat(name) from test group by fid;

+-----+--------------------+

| fid |group_concat(name) |

+-----+--------------------+

|   1 | a,b,b              |

|   2 | A,Z                |

+-----+--------------------+

selectfid,group_concat(distinct name) from test group by fid;

+-----+-----------------------------+

| fid |group_concat(distinct name) |

+-----+-----------------------------+

|   1 | a,b                         |

|   2 | A,Z                         |

+-----+-----------------------------+

2. Sort within group:

With data table comments

------------------------------------------------
| id | newsID | comment | theTime |
------------------------------------------------
| 1  |        1      |         aaa    |     11       |
------------------------------------------------
| 2  |        1      |         bbb    |     12       |
------------------------------------------------
| 3  |        2      |         ccc     |     12       |
------------------------------------------------

newsID is the news ID. each news has multiple comments, and the time is the time for comments. Now I want to see the latest comment of each news.

1) These two methods are obviously not good:

select * from comments group by newsID

In this way, for news with newId=1, the comments in the group are not sorted. mysql will display the first comments by default.

select * from comments group by newsID order by theTime desc 

It's not good either. It's out of group sorting.

2) Use the group concat function to implement sorting within a group:

select newsID,group_concat(comment,Order BY theTime DESC),theTime from comments as tt group by newsID

3) Other methods:

selet tt.id,tt.newsID,tt.comment,tt.theTime from(  
select id,newsID,comment,theTime from comments order by theTime desc) as tt group by newsID 

perhaps

select id,newsID,comment,theTime from comments as tt group by id,newsID,comment,theTime having
 theTime=(select max(theTime) from comments where newsID=tt.newsID)