Implement numbering in MySQL GROUP_CONCAT

Let us first create a table −

mysql> create table DemoTable1627     -> (     -> FirstName varchar(20),     -> LastName varchar(20)     -> ); Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command.

mysql> insert into DemoTable1627 values('John','Smith'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1627 values('John','Doe'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1627 values('Adam','Smith'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1627 values('Carol','Taylor'); Query OK, 1 row affected (0.08 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1627;

This will produce the following output −

+-----------+----------+ | FirstName | LastName | +-----------+----------+ | John      | Smith    | | John      | Doe      | | Adam      | Smith    | | Carol     | Taylor   | +-----------+----------+ 4 rows in set (0.00 sec)

Here is the query to implement group_concat() numbering −

mysql> select  LastName,     -> group_concat(     -> concat(@j := if (@p = LastName, @j + 1, if(@p := LastName,1,1)), '.', FirstName)     -> separator ', ') FirstName     -> from   DemoTable1627     -> group by LastName;

This will produce the following output −

+----------+----------------+ | LastName | FirstName      | +----------+----------------+ | Doe      | 1.John         | | Smith    | 1.John, 2.Adam | | Taylor   | 1.Carol        | +----------+----------------+ 3 rows in set (0.09 sec)