Get table column names in alphabetical order in MySQL?

MySQLMySQLi Database

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)
raja
Published on 31-Jan-2019 12:05:37
Advertisements