Get table column names in alphabetical order in MySQL?

To get the table column names in alphabetical order, you need to use ORDER BY. The syntax is as follows −

SELECT anyReferenceName.COLUMN_NAME FROM
INFORMATION_SCHEMA.COLUMNS anyReferenceName
WHERE anyReferenceName.TABLE_NAME = ’yourTableName’
ORDER BY anyReferenceName.COLUMN_NAME

First, we need to get all the columns and then we need to use ORDER BY. In the above query, we are getting all columns using INFORMATION_SCHEMA.COLUMNS.

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table ColumnsOrder
   -> (
   -> StudentFirstName varchar(20),
   -> Id int,
   -> StudentAge int,
   -> StudentLastName varchar(20)
   -> );
Query OK, 0 rows affected (0.90 sec)

Implement the above syntax to get table columns in alphabetical order.

Case 1 − By default, ORDER BY gives ascending order.

The query is as follows −

mysql> select ref.column_name from information_schema.columns ref
   -> where ref.table_name = 'ColumnsOrder'
   -> order by ref.column_name;

The following is the output −

+------------------+
| COLUMN_NAME      |
+------------------+
| Id               |
| StudentAge       |
| StudentFirstName |
| StudentLastName  |
+------------------+
4 rows in set (0.13 sec)

Case 2 − If you want in descending order, then use DESC command in the end. 

The query is as follows −

mysql> select ref.column_name from information_schema.columns ref
   -> where ref.table_name = 'ColumnsOrder'
   -> order by ref.column_name desc;

The following is the output −

+------------------+
| COLUMN_NAME      |
+------------------+
| StudentLastName  |
| StudentFirstName |
| StudentAge       |
| Id               |
+------------------+
4 rows in set (0.00 sec)