How to GROUP_CONCAT column order by where in (multiple values)?

Question

My code in mysql given below is

 select GROUP_CONCAT(table_name,'.',column_name,'  as',table_name,'_',column_name) as column_list from INFORMATION_SCHEMA.COLUMNS where table_name in ('country_details','state_details', 'city_details');

I got the result given below starts with city_details.id as city_details_id,..

Result(starts from city_details,country_details,state_details)

city_details.id as city_details_id, city_details.city_name as city_details_city_name, city_details.state_id as city_details_state_id, country_details.id as country_details_id, country_details.sortname as country_details_sortname, country_details.country_name as country_details_country_name, country_details.phonecode as country_details_phonecode, state_details.id as state_details_id, state_details.state_name as state_details_state_name, state_details.country_id as state_details_country_id

Expected Result(Starts from country_details,state_details,city_details)

country_details.id as country_details_id,country_details.sortname as country_details_sortname,country_details.country_name as country_details_country_name, country_details.phonecode as country_details_phonecode,state_details.id as state_details_id,state_details.state_name as state_details_state_name, state_details.country_id as state_details_country_id,city_details.id as city_details_id, city_details.city_name as city_details_city_name, city_details.state_id as city_details_state_id

I don't know how to fix this issue so anyone please guide me to solve this issue.

Here I used Order by Field code given below it also give the same issue.

SELECT group_concat(table_name,'.',column_name,' as  ',table_name,'_',column_name) as column_list  FROM INFORMATION_SCHEMA.COLUMNS where table_name in ('country_details','state_details', 'city_details')ORDER BY FIELD(table_name,'country_details','state_details', 'city_details');

Answer 1

The ORDER BY at the end of your query does nothing, because your query contains only one result row. (Any query with an aggregate function like COUNT or GROUP_CONCAT will contain only one row unless the query has a GROUP BY clause.)

You can put ORDER BY inside your GROUP_CONCAT aggregating function, however. Like this:

SELECT GROUP_CONCAT(
             table_name,'.',column_name,' as  ',table_name,'_',COLUMN_NAME 
             ORDER BY FIELD(table_name,'country_details','state_details', 'city_details'), 
                      ordinal_position 
       ) AS column_list
  FROM INFORMATION_SCHEMA.columns
 WHERE table_name IN ('country_details','state_details', 'city_details')

(Using ordinal_position to order puts the columns of each table in the same order they appear in the table definitions.)