0

good morning guys, I have this table named 'soccer_team'

id  first_name  surname   player_number
1   Alexis      Sanchez   7
2   Petr        Cech      33
3   Hector      Bellerin  24
4   Olivier     Giroud    12
5   Theo        Walcott   14
6   Santi       Cazorla   19

if I launch this command,

SELECT CONCAT(first_name,' ',surname,' #',player_number) as 'i' from soccer_team order by player_number

it gives me

i
Alexis Sanchez #7
Olivier Giroud #12
Theo Walcott #14
Santi Cazorla #19
Hector Bellerin #24
Petr Cech #33

which is correct

however when I run

SELECT GROUP_CONCAT(i,' ') as 'players'
FROM (SELECT CONCAT(first_name,' ',surname,' #',player_number) as 'i'
      from soccer_team
      order by player_number
     ) as a;

it gives me

players
Alexis Sanchez #7 ;Petr Cech #33 ;Hector Bellerin #24 ;Olivier Giroud #12 ;Theo Walcott #14 ;Santi Cazorla #19

while it should be

players
Alexis Sanchez #7; Olivier Giroud #12; Theo Walcott #14; Santi Cazorla #19; Hector Bellerin #24; Petr Cech #33

**

how to solve the problem I know, I wanted to know why this happens

**

UPDATE

I know how to solve it, what interests me is because it works in this way

Gianfrancesco Aurecchia
  • 750
  • 5
  • 23
  • Possible duplicate of [GROUP\_CONCAT ORDER BY](https://stackoverflow.com/questions/8631210/group-concat-order-by) – pritaeas Feb 21 '18 at 12:28

1 Answers1

1

You can specify the order inside GROUP_CONCAT provided that you include player_number in the subquery.

SELECT GROUP_CONCAT(i ORDER BY player_number ASC SEPARATOR ' ') as 'players' 
FROM (
SELECT CONCAT(first_name,' ',surname,' #',player_number) as 'i' ,player_number
from soccer_team 
) as a;

Here's a Demo.

Actually, you can simplify by removing the subquery which will still give you the same result

SELECT GROUP_CONCAT(CONCAT(first_name,' ',surname,' #',player_number) 
                     ORDER BY player_number ASC SEPARATOR ' ') as 'players' 
FROM soccerteam

Here's a Demo.

John Woo
  • 258,903
  • 69
  • 498
  • 492