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)

Arjun Thakur

Updated on 30-Jun-2020 06:31:17

• Related Questions & Answers
• C program to sort names in alphabetical order
• C program to sort names in alphabetical order using structures
• C program to sort names in alphabetical order with string functions.
• Get table names using SELECT statement in MySQL?
• Get all the column names in a table in MongoDB
• What is the syntax in MySQL to get the column names of a table?
• MySQL Quoted table/field names vs unquoted names?
• How to display the column names from a table excluding some in MySQL?
• Fetch specific rows from a MySQL table with duplicate column values (names)?
• Set Blank spaces in column names with MySQL?
• Select column names containing a string in MySQL?
• How to can I get the names of my MySQL table columns?
• Setting column values as column names in the MySQL query result?
• Lower case column names with MySQL SELECT?
• Update table and order dates in MySQL